package com.yunchang.springboot.scheduled.mwsreport;

import com.amazonaws.regions.Regions;
import com.amazonaws.services.sqs.AmazonSQS;
import com.amazonaws.services.sqs.AmazonSQSClientBuilder;
import com.amazonaws.services.sqs.model.DeleteMessageRequest;
import com.amazonaws.services.sqs.model.Message;
import com.amazonaws.services.sqs.model.ReceiveMessageRequest;
import com.yunchang.springboot.mapper.mwsreport.*;
import com.yunchang.springboot.mapper.shop.AreaMapper;
import com.yunchang.springboot.mapper.shop.ShopMapper;
import com.yunchang.springboot.models.Area;
import com.yunchang.springboot.models.Shop;
import com.yunchang.springboot.models.mwsreportbean.*;
import com.yunchang.springboot.models.sqsbean.Notification;
import com.yunchang.springboot.utils.*;
import com.yunchang.springboot.utils.amazon.AmazonMwsApi;
import com.yunchang.springboot.utils.amazon.AmazonMwsUntil;
import com.yunchang.springboot.utils.databaseutils.DatabaseUtil;
import com.yunchang.springboot.utils.xmltojavabean.JaxbUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.security.Security;
import java.time.LocalDate;
import java.util.*;
import java.util.concurrent.CountDownLatch;

import static com.google.common.collect.Lists.partition;

/**
 * @author 1
 */
@Component
@Configuration
@Service
@Slf4j
@EnableScheduling
public class ReportScheduleYc extends ReportBase {


    @Autowired
    ShopMapper shopMapper;

    @Autowired
    AreaMapper areaMapper;

    /**
     * 所有订单
     */
    @Autowired
    MwsAllOrderMapper mwsAllOrderMapper;

    /**
     * 库龄
     */
    @Autowired
    MwsInventoryAgeMapper mwsInventoryAgeMapper;

    // 每日库存记录
    @Autowired
    MwsDailyInventoryHistoryMapper mwsDailyInventoryHistoryMapper;

    // 盘库
    @Autowired
    MwsInventoryAdjustmentsMapper mwsInventoryAdjustmentsMapper;

    // 错误记录
    @Autowired
    MwsDataGetErrorRecordMapper mwsDataGetErrorRecordMapper;

    // 每月库存记录
    @Autowired
    MwsMonthlyInventoryHistoryMapper mwsMonthlyInventoryHistoryMapper;

    /**
     * 月储存费用
     */
    @Autowired
    MwsMonthlyStorageFeesMapper mwsMonthlyStorageFeesMapper;

    /**
     * 退货
     */
    @Autowired
    MwsFbaCustomerReturnsMapper mwsFbaCustomerReturnsMapper;

    /**
     * 移除订单详情
     */
    @Autowired
    MwsRemovalOrderDetailMapper mwsRemovalOrderDetailMapper;

    /**
     * 移除货件详情
     */
    @Autowired
    MwsRemovalShipmentDetailMapper mwsRemovalShipmentDetailMapper;

    /**
     * 已完成订单
     */
    @Autowired
    MwsCustomerShipmentSalesMapper mwsCustomerShipmentSalesMapper;

    /**
     * 在售商品报告
     */
    @Autowired
    MwsActiveListingsReportMapper mwsActiveListingsReportMapper;


    @Autowired
    MwsPaymentRecordEverydayMapper mwsPaymentRecordEverydayMapper;

    @Autowired
    MwsLongTermStorageFeeChargesMapper mwsLongTermStorageFeeChargesMapper;

    @Autowired
    ReportHistoryDispose reportHistoryDispose;

    @Autowired
    ReportMultithreadingDispose reportMultithreadingDispose;

    @Autowired
    ReportDateDispose reportDateDispose;


    private static final String QUEUE_NAME = "MWS_API";

    /**
     * 从SQS 队列中获取通知的最大时间
     */
    private static final Integer WRONG_TIME = 1200000;

    /**
     * 获取报告不正确标识
     */
    private static final Integer WRONG_NUMBER_SIGN = 9999;

    /**
     * payment 每日记录中的生效日期
     */
    private static final String NOW_AVAILABLE_DATE = "nowAvailableDate";

    /**
     * 时区（零时区）
     */
    private static final String TIMEZONE = "GMT";

    /**
     * ISO8601时间格式
     */
    private static final String PATTERN = "yyyy-MM-dd'T'HH:mm:ss'Z'";


    /**
     * 日常数据的获取
     *
     * @throws InterruptedException
     */
//    @Scheduled(cron = "0 03 0 * * ? ")
    public void multithreadingGetMWSReportTest() throws InterruptedException {
        multithreadingGetMWSReport();
    }

    /**
     * 历史数据的获取
     */
    @Scheduled(cron = "23 58 16 11 08 ?")
    public void multithreadingGetMWSHistoryReportRun() {
        multithreadingGetMWSHistoryReport();
    }


    /**
     * 错误任务处理
     *
     * @return
     */
    public int sendRequestReportErrorDispose(String taskName, Shop shop, Area area, String taskStartTimes, String taskEndTimes) {
        try {
            log.info("调用" + taskName + "任务开始！店铺名为：" + shop.getBrandName() + "，区域名为：" + area.getMwsCountryCode());
            String sellerId = CryptoUtil.decode(shop.getSellerId());
            String mwsAuthToken = CryptoUtil.decode(shop.getMwsAuthToken());
            String awsAccessKeyId = CryptoUtil.decode(shop.getAwsAccessKeyId());
            String secretKey = CryptoUtil.decode(shop.getSecretKey());
            Map<String, String> params = new HashMap<>(7);
            StringBuffer errorMsg = new StringBuffer();
            params.put(PARAM_AWS_ACCESS_KEY_ID, AmazonMwsUntil.urlEncode(awsAccessKeyId));
            params.put(PARAM_MWS_AUTH_TOKEN, AmazonMwsUntil.urlEncode(mwsAuthToken));
            params.put(PARAM_SELLER_ID, AmazonMwsUntil.urlEncode(sellerId));
            if (!NOT_MARKETPLACE_ID_LIST_COUNTRY_CODE.contains(area.getMwsCountryCode())) {
                params.put(PARAM_MARKETPLACE_ID_LIST_ID, AmazonMwsUntil.urlEncode(area.getMarketplaceId()));
            }
            params.put("taskName", taskName);
            params.put("shopName", shop.getBrandName());
            params.put("areaCode", area.getMwsCountryCode());
            // 清除SQS队列中的信息
//            deleteNotificationMessage();
            // 发送RequestReport请求 sendRequestHistoryReport
//            Map<String, Map<String, String>> requestReportIdAndParamMap = sendRequestReport(taskName, area, secretKey, params);
//            Map<String, Map<String, String>> requestReportIdAndParamMap = sendRequestHistoryReport(taskName, area, secretKey, params, taskStartTimes, taskEndTimes);
            Map<String, Map<String, String>> requestReportIdAndParamMap = reportMultithreadingDispose.sendRequestReportByStartTimeAndEndTime(taskName, area, secretKey, params, taskStartTimes, taskEndTimes);
            if (null == requestReportIdAndParamMap) {
                log.error("发送requestReport请求失败，请求结果为空，详情请看报错信息！");
                return 1;
            }
            if (area.getMwsCountryCode().equals(MX_AREA)) {
                //墨西哥区域通过GetReportList获取ReportId
                Map<String, Map<String, String>> reportIdAndParamMapByGetReportList = new HashMap<>();
                for (String key : requestReportIdAndParamMap.keySet()) {
                    Map<String, String> paramMap = requestReportIdAndParamMap.get(key);
                    paramMap.put("ReportRequestId", key);
                    paramMap.put(PARAM_REPORT_REQUEST_ID_LIST_ID, key);
                    // 发送GetReportList请求
                    reportIdAndParamMapByGetReportList = sendGetReportList(paramMap);
                }
                if (null != reportIdAndParamMapByGetReportList || reportIdAndParamMapByGetReportList.size() > 0) {
                    for (String key : reportIdAndParamMapByGetReportList.keySet()) {
                        Map<String, String> paramMap = reportIdAndParamMapByGetReportList.get(key);
                        paramMap.put("ReportId", key);
                        // 发送GetReport请求
                        sendGetReport(paramMap, taskName, shop.getBrandName(), area.getMwsCountryCode());
                    }
                } else {
                    log.error("通过GetReportList获取reportId报错，reportIdAndParamMap为空！" + reportIdAndParamMapByGetReportList);
                    return 1;
                }
            } else {
                // 从通知中获取reportId   返回reportId集合
                Map<String, Map<String, String>> reportIdAndParamMap = getNotificationSingleton(requestReportIdAndParamMap, errorMsg);
                if (null != reportIdAndParamMap && reportIdAndParamMap.size() > 0) {
                    if (!reportIdAndParamMap.containsKey("noData") && !reportIdAndParamMap.containsKey("reportCancelled")) {
                        // 发送getReport请求，并将报告内容插入数据库
                        for (String key : reportIdAndParamMap.keySet()) {
                            Map<String, String> paramMap = reportIdAndParamMap.get(key);
                            paramMap.put("ReportId", key);
                            sendGetReport(paramMap, taskName, shop.getBrandName(), area.getMwsCountryCode());
                        }
                    }
                    if (reportIdAndParamMap.containsKey("reportCancelled")) {
                        log.error("任务名为：" + taskName + "店铺名为：" + shop.getBrandName() + "区域名为：" + area.getMwsCountryCode() + "报告被取消！");
                        return 1;
                    }
                } else {
                    log.error("从SQS队列中获取报告通知失败！");
                    return 1;
                }
            }
        } catch (Exception e) {
            log.error("出错了:" + e + " 任务名为：" + taskName + "店铺名为：" + shop.getBrandName() + "区域名为：" + area.getMwsCountryCode());
            return 1;
        } finally {
            log.info("***********获取------" + taskName + "-----定时任务结束**********");
        }
        return 0;
    }

    /**
     * 多线程获取mws报告  日常报告数据获取
     */
    public void multithreadingGetMWSReport() {
        System.setProperty("https.protocols", "TLSv1,TLSv1.1,TLSv1.2");
        // 设置无限策略
        Security.setProperty("crypto.policy", "unlimited");
        for (int i = 0; i < TASK_NAME_SUBSCRIPTIONS.size(); i++) {
            // 判断该报告是否是每月获取一次的报告
            if (TASK_NAME_SUBSCRIPTIONS_EVERY_MONTH.contains(TASK_NAME_SUBSCRIPTIONS.get(i))) {
                if (TASK_NAME_SUBSCRIPTIONS.get(i).equals("长期仓储费")) {
                    if (LocalDate.now().getDayOfMonth() != 18) {
                        log.info(TASK_NAME_SUBSCRIPTIONS.get(i) + "每月十八日获取报告，现在是：" + LocalDate.now().getDayOfMonth() + "日");
                        continue;
                    }
                } else {
                    if (LocalDate.now().getDayOfMonth() != 10) {
                        log.info(TASK_NAME_SUBSCRIPTIONS.get(i) + "每月十日获取报告，现在是：" + LocalDate.now().getDayOfMonth() + "日");
                        continue;
                    }
                }
            }
            //获取系统时间
            long s = System.currentTimeMillis();
            // 店铺及区域集合
            List<Map<Shop, Area>> shopAndAreaMapList = getShopNameAndAreaByTaskName(TASK_NAME_SUBSCRIPTIONS.get(i));
            //线程启动个数限制
            CountDownLatch countDownLatch = new CountDownLatch(shopAndAreaMapList.size());
//            // 清除SQS队列中的信息
//            deleteNotificationMessage();
            String taskStartTimes = null;
            String taskEndTimes = null;
            Map<String, String> reportStartAndEndTimeByTaskNameMap = getReportStartAndEndTimeByTaskName(TASK_NAME_SUBSCRIPTIONS.get(i));
            if(null != reportStartAndEndTimeByTaskNameMap && !reportStartAndEndTimeByTaskNameMap.isEmpty()){
                if(!StringUtils.isEmpty(reportStartAndEndTimeByTaskNameMap.get("startTime")) && !StringUtils.isEmpty(reportStartAndEndTimeByTaskNameMap.get("endTime"))){
                    taskStartTimes = reportStartAndEndTimeByTaskNameMap.get("startTime");
                    taskEndTimes = reportStartAndEndTimeByTaskNameMap.get("endTime");
                }
            }
            for (Map<Shop, Area> shopAndAreaMap : shopAndAreaMapList) {
                for (Shop shopKey : shopAndAreaMap.keySet()) {
                    reportMultithreadingDispose.syncSendRequestDailyReport(countDownLatch, TASK_NAME_SUBSCRIPTIONS.get(i), shopKey, shopAndAreaMap.get(shopKey),taskStartTimes,taskEndTimes);
                }
            }
            try {
                countDownLatch.await();
                log.info(TASK_NAME_SUBSCRIPTIONS.get(i) + "获取报表的异步线程调用完毕！");
                long e = System.currentTimeMillis();
                log.info("总耗时:" + (e - s));
                //耗时太短可以在此处加上时间限制
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        // 错误处理
        mwsHistoryReportErrorDispose();
//        log.info("***********全部定时任务的错误处理**********");
//        // 错误报告是否全部处理标识
//        Boolean isErrorReport = true;
//        int isErrorReportNum = 0;
//        int isErrorReportMaxNum = 3;
//        while (isErrorReport) {
//            isErrorReportNum++;
//            List<MwsDataGetErrorRecord> dataGetErrorRecordList = mwsDataGetErrorRecordMapper.selectByType(1);
//            if (null == dataGetErrorRecordList || dataGetErrorRecordList.size() == 0) {
//                isErrorReport = false;
//                log.info("***********本次定时任务全部数据以获取**********");
//            }
//            if (isErrorReportNum == isErrorReportMaxNum) {
//                log.info("已经获取了两次错误报告，是时候结束了！！！");
//                List<Long> idList = mwsDataGetErrorRecordMapper.selectIdListByType(1);
//                if (null != idList && !idList.isEmpty()) {
//                    mwsDataGetErrorRecordMapper.updateTypeById(idList);
//                }
//                break;
//            }
//            try {
//                if (isErrorReportNum != 1) {
//                    synchronized (this) {
//                        this.wait(14500000);
//                    }
//                }
//            } catch (InterruptedException e) {
//                e.printStackTrace();
//            }
//            for (MwsDataGetErrorRecord errorData : dataGetErrorRecordList) {
//                Integer isRightSign = baseHandleByErrorTask(errorData.getName(), shopMapper.getOneInfoByBrandName(errorData.getShop()), errorData.getArea());
//                if (isRightSign == 0) {
//                    if (null != errorData.getId()) {
//                        mwsDataGetErrorRecordMapper.updateIsHandle(errorData.getId());
//                    }
//                }
//            }
//            List<MwsDataGetErrorRecord> dataGetErrorRecordResultList = mwsDataGetErrorRecordMapper.selectByType(1);
//            if (null == dataGetErrorRecordResultList || dataGetErrorRecordResultList.size() == 0) {
//                log.info("***********获取----- ----错误任务重新成功，已拿到数据**********");
//            }
//        }
    }

    /**
     * 历史报告数据的获取
     */
    public void multithreadingGetMWSHistoryReport() {
        String taskStartTimes;
        String taskEndTimes;
        for (int index = 24; index > 0; index--) {
            for (int i = 0; i < TASK_NAME_SUBSCRIPTIONS_TEST_HISTORY.size(); i++) {
                //获取系统时间
                long s = System.currentTimeMillis();
                List<Map<Shop, Area>> shopAndAreaMapList = getShopNameAndAreaByTaskName(TASK_NAME_SUBSCRIPTIONS_TEST_HISTORY.get(i));
                //线程启动个数限制
                CountDownLatch countDownLatch = new CountDownLatch(shopAndAreaMapList.size());
                for (Map<Shop, Area> shopAndAreaMap : shopAndAreaMapList) {
                    for (Shop shopKey : shopAndAreaMap.keySet()) {
                        Area area = shopAndAreaMap.get(shopKey);
                        taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.getMonthFirstDayByString(new Date(), -index, TIMEZONE, PATTERN, area.getMwsCountryCode()));
                        taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.getMonthLastDayByDate(new Date(), -index, TIMEZONE, PATTERN, area.getMwsCountryCode()));
                        log.info("任务的开始时间：" + taskStartTimes + "任务名为：" + TASK_NAME_SUBSCRIPTIONS_TEST_HISTORY.get(i) + "店铺名为:" + shopKey.getBrandName() + "区域名为：" + area.getMwsCountryCode());
                        log.info("任务的结束时间：" + taskEndTimes + "任务名为：" + TASK_NAME_SUBSCRIPTIONS_TEST_HISTORY.get(i) + "店铺名为:" + shopKey.getBrandName() + "区域名为：" + area.getMwsCountryCode());
//                        reportHistoryDispose.syncSendRequestHistoryReport(countDownLatch, TASK_NAME_SUBSCRIPTIONS_TEST_HISTORY.get(i), shopKey, shopAndAreaMap.get(shopKey), taskStartTimes, taskEndTimes);
//                        syncSendRequestHistoryReport(countDownLatch, TASK_NAME_SUBSCRIPTIONS_TEST_HISTORY.get(i), shopKey, shopAndAreaMap.get(shopKey), taskStartTimes, taskEndTimes);
                        reportMultithreadingDispose.syncSendRequestHistoryReport(countDownLatch, TASK_NAME_SUBSCRIPTIONS_TEST_HISTORY.get(i), shopKey, shopAndAreaMap.get(shopKey), taskStartTimes, taskEndTimes);
                    }
                }
                try {
                    countDownLatch.await();
                    log.info(TASK_NAME_SUBSCRIPTIONS_TEST_HISTORY.get(i) + "获取报表的异步线程调用完毕！");
                    long e = System.currentTimeMillis();
                    log.info("总耗时:" + (e - s));
                    //耗时太短可以在此处加上时间限制
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                // 连续获取两次后暂停15分钟
                if (index % 2 == 0) {
                    try {
                        synchronized (this) {
                            this.wait(900000);
                        }
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        // 错误处理
        mwsHistoryReportErrorDispose();
    }

    public void mwsHistoryReportErrorDispose() {
        log.info("***********全部定时任务的错误处理**********");
        // 错误报告是否全部处理标识
        Boolean isErrorReport = true;
        // 错误处理次数初始值
        int isErrorReportNum = 0;
        // 错误处理次数最大值
        int isErrorReportMaxNum = 4;
        String taskStartTimes;
        String taskEndTimes;
        while (isErrorReport) {
            isErrorReportNum++;
            // 获取type为1,isHandle为0的错误记录
            List<MwsDataGetErrorRecord> dataGetErrorRecordList = mwsDataGetErrorRecordMapper.selectByType(1);
            if (null == dataGetErrorRecordList || dataGetErrorRecordList.size() == 0) {
                log.info("***********本次错误处理任务全部完成**********");
                break;
            }
            if (isErrorReportNum == isErrorReportMaxNum) {
                log.info("已经获取了三次错误报告，是时候结束了！！！");
                List<Long> idList = mwsDataGetErrorRecordMapper.selectIdListByType(1);
                if (null != idList && !idList.isEmpty()) {
                    mwsDataGetErrorRecordMapper.updateTypeById(idList);
                }
                break;
            }
            try {
//                if (isErrorReportNum != 1) {
                synchronized (this) {
                    this.wait(14500000);
                }
//                }
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            for (MwsDataGetErrorRecord errorData : dataGetErrorRecordList) {
                Area area = areaMapper.getAreaByMWSCountryCode(errorData.getArea());
                taskStartTimes = errorData.getTaskBeginTime();
                taskEndTimes = errorData.getTaskEndTime();
                log.info("出现错误的报告任务的开始时间：" + taskStartTimes + "任务名为：" + errorData.getName() + "店铺名为:" + errorData.getShop() + "区域名为：" + area.getMwsCountryCode());
                log.info("出现错误的报告任务的结束时间：" + taskEndTimes + "任务名为：" + errorData.getName() + "店铺名为:" + errorData.getShop() + "区域名为：" + area.getMwsCountryCode());
                Integer isRightSign = sendRequestReportErrorDispose(errorData.getName(), shopMapper.getOneInfoByBrandName(errorData.getShop()), area, taskStartTimes, taskEndTimes);
                if (isRightSign == 0) {
                    if (null != errorData.getId()) {
                        mwsDataGetErrorRecordMapper.updateIsHandle(errorData.getId());
                    }
                }
            }
            List<MwsDataGetErrorRecord> dataGetErrorRecordResultList = mwsDataGetErrorRecordMapper.selectByType(1);
            if (null == dataGetErrorRecordResultList || dataGetErrorRecordResultList.size() == 0) {
                log.info("***********本次错误处理任务全部完成**********");
                break;
            }
        }
    }


    /**
     * payment报告逻辑处理
     */
    public void paymentHandle() {
        String taskName = "payment";
        log.info("***********获取------" + taskName + "-----定时任务开始**********");
        List<Shop> shopList = shopMapper.getAllList();
        String shopBrandName = null;
        String areaMWSCountryName = null;
        try {
            for (Shop shop : shopList) {
                // shop和area的map（包含了这个店铺的所有信息以及区域信息，shop 和 area）
                Map<String, Object> map = beginScheduleTaskValidate(shop);
                if (null == map) {
                    log.warn("×××××" + taskName + "×××定时任务出错××××××××××××××××××");
                    return;
                }
                shopBrandName = shop.getBrandName();
                Map<String, Object> shopMap = (Map<String, Object>) map.get("shop");
                String sellerId = String.valueOf(shopMap.get(COLUMN_SELLER_ID));
                String mwsAuthToken = String.valueOf(shopMap.get(COLUMN_MWS_AUTH_TOKEN));
                String awsAccessKeyId = String.valueOf(shopMap.get(COLUMN_AWS_ACCESS_KEY_ID));
                String secretKey = String.valueOf(shopMap.get(COLUMN_SECRET_KEY));
                List<Area> areaList = (List<Area>) map.get("area");
                Map<String, String> params = new HashMap<>(10);
                StringBuffer errorMsg = new StringBuffer();
                for (int index = 0; index < areaList.size(); index++) {
                    Area area = areaList.get(index);
                    areaMWSCountryName = area.getMwsCountryCode();
                    try {
                        String availableDate = mwsPaymentRecordEverydayMapper.getAvailableDateBySellerIdAndArea(CryptoUtil.encode(sellerId), area.getMwsCountryCode());
                        params.put(NOW_AVAILABLE_DATE, availableDate);
                        params.put(PARAM_AWS_ACCESS_KEY_ID, AmazonMwsUntil.urlEncode(awsAccessKeyId));
                        params.put(PARAM_MWS_AUTH_TOKEN, AmazonMwsUntil.urlEncode(mwsAuthToken));
                        params.put(PARAM_SELLER_ID, AmazonMwsUntil.urlEncode(sellerId));
                        if (!NOT_MARKETPLACE_ID_LIST_COUNTRY_CODE.contains(area.getMwsCountryCode())) {
                            params.put(PARAM_MARKETPLACE_ID_LIST_ID, AmazonMwsUntil.urlEncode(area.getMarketplaceId()));
                        }
                        params.put("taskName", taskName);
                        params.put("shopName", shop.getShopName());
                        params.put("areaCode", area.getMwsCountryCode());
                        params.put("SecretKey", secretKey);
                        params.put("mwsUrl", area.getMwsEndPoint());
                        params.put("ReportType", PAYMENT_ENUMERATION);
                        // 获取报告数据
                        Map<String, Object> paymentMap = sendGetReportListByPayment(params);
                        if (null == paymentMap) {
                            errorMsg.append("发送GetReportList出错,详情请看日志信息;");
                            logWarnErrorMsg(taskName, String.valueOf(shopMap.get(COLUMN_SHOP_BRAND_NAME)), area.getMwsCountryCode(), errorMsg);
                            errorMsg = new StringBuffer();
                            continue;
                        }
                        if (paymentMap.containsKey(REPORT_ID)) {
                            // 获取发送getReport的参数
                            Map<String, String> getReportParamMap = new HashMap<>();
                            for (Map.Entry<String, Object> entry : paymentMap.entrySet()) {
                                if (entry.getKey().equals(PARAM_MAP)) {
                                    continue;
                                }
                                getReportParamMap.put(entry.getKey(), entry.getValue().toString());
                            }
                            // 发送GetReport 请求
                            sendGetReport(getReportParamMap, taskName, shopBrandName, areaMWSCountryName);
                        }
                        // 店铺第一次获取payment报告，需要将最近日期的报告存储下来
                        if (StringUtils.isEmpty(availableDate)) {
                            Map<String, Object> reportInfoMap = new HashMap<>();
                            if (paymentMap.containsKey(PARAM_MAP)) {
                                reportInfoMap = (Map<String, Object>) paymentMap.get(PARAM_MAP);
                            }
                            MwsPaymentRecordEveryday mwsPaymentRecordEveryday = new MwsPaymentRecordEveryday();
                            mwsPaymentRecordEveryday.setReportType(reportInfoMap.get("ReportType").toString());
                            mwsPaymentRecordEveryday.setReportId(reportInfoMap.get("ReportId").toString());
                            mwsPaymentRecordEveryday.setReportRequestId(reportInfoMap.get("ReportRequestId").toString());
                            mwsPaymentRecordEveryday.setAvailableDate(DateUtil.ISO8601ToGMT(reportInfoMap.get("AvailableDate").toString()));
                            mwsPaymentRecordEveryday.setSellerId(CryptoUtil.encode(sellerId));
                            mwsPaymentRecordEveryday.setArea(area.getMwsCountryCode());
                            mwsPaymentRecordEverydayMapper.insertSelective(mwsPaymentRecordEveryday);
                        }
                        errorMsg = new StringBuffer();
                    } catch (Exception e) {
                        errorMsg.append("流程中出现异常，").append(e);
                        logWarnErrorMsg(taskName, String.valueOf(shopMap.get(COLUMN_SHOP_BRAND_NAME)), area.getMwsCountryCode(), errorMsg);
                    }
                }
            }
        } catch (Exception e) {
            log.warn("×××××" + taskName + "×××定时任务出错××××××××××详情:" + e);
            logWarnErrorMsg(taskName, shopBrandName, areaMWSCountryName, new StringBuffer(e.toString()));
        } finally {
            log.info("***********获取------" + taskName + "-----定时任务结束**********");
        }
    }

    /**
     * 通用逻辑处理之出现错误的任务重新调用
     *
     * @param taskName
     * @param shop
     * @return 0：获取数据成功，1：获取数据失败
     */
    public int baseHandleByErrorTask(String taskName, Shop shop, String taskArea) {
        log.info("***********获取------" + taskName + "-----错误处理任务开始**********" + "店铺名为：" + shop.getShopName() + "区域名为：" + taskArea);
        try {
            Map<String, Object> map = beginScheduleTaskValidate(shop);
            if (null == map) {
                log.warn("×××××" + taskName + "×××定时任务出错××××××××××××××××××");
                return 1;
            }
            Map<String, Object> shopMap = (Map<String, Object>) map.get("shop");
            String sellerId = String.valueOf(shopMap.get(COLUMN_SELLER_ID));
            String mwsAuthToken = String.valueOf(shopMap.get(COLUMN_MWS_AUTH_TOKEN));
            String awsAccessKeyId = String.valueOf(shopMap.get(COLUMN_AWS_ACCESS_KEY_ID));
            String secretKey = String.valueOf(shopMap.get(COLUMN_SECRET_KEY));
            Area area = areaMapper.getAreaByMWSCountryCode(taskArea);
            Map<String, String> params = new HashMap<>(6);
            StringBuffer errorMsg = new StringBuffer();
            try {
                params.put(PARAM_AWS_ACCESS_KEY_ID, AmazonMwsUntil.urlEncode(awsAccessKeyId));
                params.put(PARAM_MWS_AUTH_TOKEN, AmazonMwsUntil.urlEncode(mwsAuthToken));
                params.put(PARAM_SELLER_ID, AmazonMwsUntil.urlEncode(sellerId));
                if (!NOT_MARKETPLACE_ID_LIST_COUNTRY_CODE.contains(area.getMwsCountryCode())) {
                    params.put(PARAM_MARKETPLACE_ID_LIST_ID, AmazonMwsUntil.urlEncode(area.getMarketplaceId()));
                }
                params.put("taskName", taskName);
                params.put("shopName", shop.getShopName());
                params.put("areaCode", area.getMwsCountryCode());
                // 发送RequestReport请求
                Map<String, Map<String, String>> requestReportIdAndParamMap = sendRequestReport(taskName, area, secretKey, params);
                if (null == requestReportIdAndParamMap) {
                    return 1;
                }
                if (area.getMwsCountryCode().equals(MX_AREA)) {
                    //墨西哥区域通过GetReportList获取ReportId
                    Map<String, Map<String, String>> reportIdAndParamMapByGetReportList = new HashMap<>();
                    for (String key : requestReportIdAndParamMap.keySet()) {
                        Map<String, String> paramMap = requestReportIdAndParamMap.get(key);
                        paramMap.put("ReportRequestId", key);
                        reportIdAndParamMapByGetReportList = sendGetReportList(paramMap);
                    }
                    if (null != reportIdAndParamMapByGetReportList || reportIdAndParamMapByGetReportList.size() > 0) {
                        for (String key : reportIdAndParamMapByGetReportList.keySet()) {
                            Map<String, String> paramMap = reportIdAndParamMapByGetReportList.get(key);
                            paramMap.put("ReportId", key);
                            sendGetReport(paramMap, taskName, shop.getBrandName(), area.getMwsCountryCode());
                        }
                    } else {
                        log.error("通过GetReportList获取reportId报错，reportIdAndParamMap为空！" + reportIdAndParamMapByGetReportList);
                        return 1;
                    }
                } else {
                    // 从通知中获取reportId   返回reportId集合
                    Map<String, Map<String, String>> reportIdAndParamMap = getNotificationSingleton(requestReportIdAndParamMap, errorMsg);
                    if (null != reportIdAndParamMap && reportIdAndParamMap.size() > 0) {
                        // 发送getReport请求，并将报告内容插入数据库
                        for (String key : reportIdAndParamMap.keySet()) {
                            Map<String, String> paramMap = reportIdAndParamMap.get(key);
                            paramMap.put("ReportId", key);
                            sendGetReport(paramMap, taskName, shop.getBrandName(), taskArea);
                        }
                    } else {
                        return 1;
                    }
                }
            } catch (Exception e) {
                return 1;
            }
        } catch (Exception e) {
            log.warn("×××××" + taskName + "×××定时任务出错××××××××××详情:" + e);
            return 1;
        }
        log.info("***********获取------" + taskName + "-----错误处理任务结束**********" + "店铺名为：" + shop.getShopName() + "区域名为：" + taskArea);
        return 0;
    }

    /**
     * 获取SQS队列中的通知  单线程
     */
    public synchronized Map<String, Map<String, String>> getNotificationSingleton(Map<String, Map<String, String>> requestReportAndParamMap, StringBuffer errorMsg) throws InterruptedException {
        // 存放参数中集合
        Map<String, String> paramMapByLog = new HashMap<>(1);
        for (String key : requestReportAndParamMap.keySet()) {
            paramMapByLog = requestReportAndParamMap.get(key);
        }
        // 没有拿到通知的次数
        int isNullNotificationNum = 1;
        // 允许拿不到通知的最大次数
        int isNullNotificationMaxNum = 5;
        // 返回结果集合
        Map<String, Map<String, String>> reportIdAndParamMap = new HashMap<>(1);
        // 添加java系统属性（临时变量）
        System.setProperty("aws.accessKeyId", AWS_ACCESS_KEY_ID);
        System.setProperty("aws.secretKey", AWS_SECRET_ACCESS_KEY);
        // 指定区域
        AmazonSQS sqs = AmazonSQSClientBuilder.standard().withRegion(Regions.AP_NORTHEAST_2).build();
        // 获取队列的URL
        String queue_url = sqs.getQueueUrl(QUEUE_NAME).getQueueUrl();
        String bodyXml = null;
        String receiptHandle = null;
        // 配置消息接收的长轮询
        final ReceiveMessageRequest receive_request = new ReceiveMessageRequest()
                .withQueueUrl(queue_url)
                .withWaitTimeSeconds(20)
                .withMaxNumberOfMessages(1);
        List<Message> longPollingMessages = sqs.receiveMessage(receive_request).getMessages();

        // 若SQS队列中无通知处理
        do {
            if (null == longPollingMessages || longPollingMessages.size() == 0) {
                log.warn("任务名为：" + paramMapByLog.get("taskName") + " 店铺名为：" + paramMapByLog.get("shopName") + "区域为：" + paramMapByLog.get("areaCode") + "的报告SQS队列中没有该通知，第：" + isNullNotificationNum + "次");
                isNullNotificationNum++;
                Thread.sleep(10000 * isNullNotificationNum);
                longPollingMessages = sqs.receiveMessage(receive_request).getMessages();
            } else {
                break;
            }
        }
        while (isNullNotificationNum < isNullNotificationMaxNum);

        // 判断队列中是否有我们需要的通知，不需要的直接删除
        Boolean isUsedNotification = true;
        int isFirst = 0;
        //获取当前系统时间，作为轮询SQS队列的开始时间
        long startTime = System.currentTimeMillis();
        DeleteMessageRequest deleteMessageRequest = new DeleteMessageRequest();
        do {
            if (isFirst != 0) {
                longPollingMessages = sqs.receiveMessage(receive_request).getMessages();
            }
            isFirst++;
            for (Message longPollingMessage : longPollingMessages) {
                bodyXml = longPollingMessage.getBody();
                receiptHandle = longPollingMessage.getReceiptHandle();
            }
            Notification notification = JaxbUtils.jaxbConvertXmlToBean(bodyXml, new Notification().getClass());
            if (null == notification.getNotificationPayload().getReportProcessingFinishedNotification()) {
                continue;
            }
            String reportRequestId = notification.getNotificationPayload().getReportProcessingFinishedNotification().getReportRequestId();
            String reportProcessingStatus = notification.getNotificationPayload().getReportProcessingFinishedNotification().getReportProcessingStatus();
            String reportId = notification.getNotificationPayload().getReportProcessingFinishedNotification().getReportId();
            Boolean isUsedRequestReportId = true;
            // 如果 reportRequestId 为空，说明此报告是其他类型的报告
            if (null == reportRequestId) {
                deleteMessageRequest.setQueueUrl(queue_url);
                deleteMessageRequest.setReceiptHandle(receiptHandle);
                sqs.deleteMessage(deleteMessageRequest);
            }
            if (requestReportAndParamMap.containsKey(reportRequestId)) {
                isUsedNotification = false;
                isUsedRequestReportId = false;
                Map<String, String> paramMap = new HashMap<>();

                paramMap.putAll(requestReportAndParamMap.get(reportRequestId));
                switch (reportProcessingStatus) {
                    case "DONE":
                        reportIdAndParamMap.put(reportId, paramMap);
                        deleteMessageRequest.setQueueUrl(queue_url);
                        deleteMessageRequest.setReceiptHandle(receiptHandle);
                        sqs.deleteMessage(deleteMessageRequest);
                        break;
                    case "DONE_NO_DATA":
                        log.info("任务名为：" + paramMap.get("taskName") + " 店铺名为：" + paramMap.get("shopName") + "区域为：" + paramMap.get("areaCode") + "的报告无数据");
                        reportIdAndParamMap.put("noData", paramMap);
                        deleteMessageRequest.setQueueUrl(queue_url);
                        deleteMessageRequest.setReceiptHandle(receiptHandle);
                        sqs.deleteMessage(deleteMessageRequest);
                        break;
                    case "CANCELLED":
                        errorMsg.append("任务名为：" + paramMap.get("taskName") + " 店铺名为：" + paramMap.get("shopName") + "区域为：" + paramMap.get("areaCode") + "的报告被取消");
                        reportIdAndParamMap.put("reportCancelled", paramMap);
                        deleteMessageRequest.setQueueUrl(queue_url);
                        deleteMessageRequest.setReceiptHandle(receiptHandle);
                        sqs.deleteMessage(deleteMessageRequest);
                        break;
                    default: {
                    }
                }
            }
            if (isUsedRequestReportId) {
                deleteMessageRequest.setQueueUrl(queue_url);
                deleteMessageRequest.setReceiptHandle(receiptHandle);
                sqs.deleteMessage(deleteMessageRequest);
                long endTime = System.currentTimeMillis();
                if (endTime - startTime > WRONG_TIME) {
                    errorMsg.append("轮询SQS队列时未发现任务名为：" + paramMapByLog.get("taskName") + " 店铺名为：" + paramMapByLog.get("shopName") + "区域为：" + paramMapByLog.get("areaCode") + "的报告");
                    isUsedNotification = false;
                }
            }

        }
        while (isUsedNotification);
        return reportIdAndParamMap;
    }


    /**
     * 删除SQS队列中的信息
     */
    public synchronized void deleteNotificationMessage() {
        try {
            //获取当前系统时间，作为轮询SQS队列的开始时间
            long startTime = System.currentTimeMillis();
            // 添加java系统属性（临时变量）
            System.setProperty("aws.accessKeyId", AWS_ACCESS_KEY_ID);
            System.setProperty("aws.secretKey", AWS_SECRET_ACCESS_KEY);
            // 指定区域
            AmazonSQS sqs = AmazonSQSClientBuilder.standard().withRegion(Regions.AP_NORTHEAST_2).build();
            // 获取队列的URL
            String queue_url = sqs.getQueueUrl(QUEUE_NAME).getQueueUrl();
            String receiptHandle;
            // 配置消息接收的长轮询
            final ReceiveMessageRequest receive_request = new ReceiveMessageRequest()
                    .withQueueUrl(queue_url)
                    .withWaitTimeSeconds(20)
                    .withMaxNumberOfMessages(10);
            while (true) {
                List<Message> longPollingMessages = sqs.receiveMessage(receive_request).getMessages();
                if (null != longPollingMessages && longPollingMessages.size() > 0) {
                    for (Message longPollingMessage : longPollingMessages) {
                        receiptHandle = longPollingMessage.getReceiptHandle();
                        if (null != receiptHandle) {
                            sqs.deleteMessage(new DeleteMessageRequest(queue_url, receiptHandle));
                        }
                    }
                } else {
                    long endTime = System.currentTimeMillis();
                    log.info("删除SQS队列信息用时：" + (endTime - startTime));
                    log.info("SQS队列中已没有信息");
                    return;
                }
            }
        } catch (Exception e) {
            log.error("删除SQS中信息时出现异常" + e);
        }
    }


    /**
     * @param params
     * @return
     * @throws Exception
     */
    @Transactional(rollbackFor = Exception.class)
    public List<List<String>> sendGetReport(Map<String, String> params, String taskName, String shopName, String areaName) throws Exception {
        List<Object> insertList = null;
        //发送GetReport报告获取报告
        Map<String, Map<String, Object>> reportAndParamMap = handleGetReport(params);
        // 根据任务名将报告数据存入数据库
        String taskNameKey = null;
        Map<String, Object> reportListAndParam = new HashMap<>();
        for (String key : reportAndParamMap.keySet()) {
            taskNameKey = key;
            reportListAndParam.putAll(reportAndParamMap.get(key));
        }
        switch (taskNameKey) {
            case "预留库存": {
                insertList = handleReservedInventory((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "管理亚马逊库存": {
                insertList = handleManageFbaInventory((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "库龄": {
                insertList = handleInventoryAge((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "所有订单": {
                insertList = handleAllOrder((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "盘库": {
                insertList = handleInventory((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "已接收库存": {
                insertList = handleReceiveInventory((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "每月库存记录": {
                insertList = handleEveryMonthInventoryRecords((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "月储存费用": {
                insertList = handleMonthlyStorageFees((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "长期仓储费": {
                insertList = handleLongTermStorageFeeCharges((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "退货": {
                insertList = handleFbaCustomerReturns((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "移除订单详情": {
                handleRemovalOrderDetail((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "移除货件详情": {
                insertList = handleRemovalShipmentDetail((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "费用预览": {
                handleFeePreview((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "已完成订单": {
                insertList = handleCompleteOrder((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "赔偿": {
                insertList = handleReimbursements((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "每日库存记录": {
                insertList = handleEveryDayInventoryRecords((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "库存调整": {
                insertList = handleInventoryReconciliation((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "库存仓储超量费": {
                insertList = handleInventoryStorageOverageFees((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            case "在售商品报告": {
                insertList = handleActiveListingsReport((List<List<String>>) reportListAndParam.get("reportList"), String.valueOf(reportListAndParam.get("shopName")), String.valueOf(reportListAndParam.get("areaCode")));
            }
            break;
            default: {
            }
        }
        try {
            List<Object> insertListCopy;
            if (null != insertList && !insertList.isEmpty()) {
                if (insertList.size() > INSERT_MAX_NUM) {
                    // 将insertList数量大于INSERT_MAX_NUM的进行切割并返回数量为INSERT_MAX_NUM大小的集合
                    List<List<Object>> subsectionResultList = partition(insertList, INSERT_MAX_NUM);
                    for (int i = 0; i < subsectionResultList.size(); i++) {
                        insertListCopy = subsectionResultList.get(i);
                        baseInsertList(insertListCopy);
                    }
                } else {
                    baseInsertList(insertList);
                }
            } else {
                baseInsertList(insertList);
            }
        } catch (Exception e) {
            logWarnErrorMsgByInsertData(taskName, shopName, areaName, new StringBuffer("插入数据时出现错误" + e));
            log.error("插入数据时出现错误，" + e);
        }
        return null;
    }


    /**
     * 发送RequestReport请求获取ReportRequestId
     *
     * @param taskName
     * @param area
     * @param secretKey
     * @param params
     * @return
     * @throws Exception
     */
    public Map<String, Map<String, String>> sendRequestReport(String taskName, Area area, String secretKey, Map<String, String> params) throws Exception {
        Map<String, Map<String, String>> requestReportIdAndParamMap = null;
        switch (taskName) {
            case "预留库存": {
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", RESERVED_INVENTORY_ENUMERATION);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "管理亚马逊库存": {
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", MANAGE_FBA_INVENTORY_ENUMERATION);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "库龄": {
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", INVENTORY_AGE_ENUMERATION);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "所有订单": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDay(-30))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDayLastTime(0))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", ALL_ORDER_ENUMERATION);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "盘库": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDay(-2))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDayLastTime(-2))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", INVENTORY_ADJUSTMENTS);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "已接收库存": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDay(-2))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDayLastTime(-2))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", RECEIVED_INVENTORY);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "每月库存记录": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getMonthFirstDayByDate(new Date(), -1))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getMonthLastDayByDate(new Date(), -1))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", MONTHLY_INVENTORY_HISTORY);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "月储存费用": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getMonthFirstDayByDate(new Date(), -1))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getMonthLastDayByDate(new Date(), -1))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", MONTHLY_STORAGE_FEES);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "长期仓储费": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getMonthFirstDayByDate(new Date(), -1))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getMonthFirstDayByDate(new Date(), -1))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", LONG_TERM_STORAGE_FEE_CHARGES);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "退货": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDay(-2))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDayLastTime(-2))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", FBA_CUSTOMER_RETURNS);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "移除订单详情": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDay(-2))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDayLastTime(-2))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", REMOVAL_ORDER_DETAIL);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "移除货件详情": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDay(-2))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDayLastTime(-2))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", REMOVAL_SHIPMENT_DETAIL);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "费用预览": {
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", FEE_PREVIEW);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "已完成订单": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDay(-2))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDayLastTime(-2))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", CUSTOMER_SHIPMENT_SALES);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "赔偿": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDay(-2))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDayLastTime(-2))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", REIMBURSEMENTS);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "每日库存记录": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDay(-2))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getFutureDateDayLastTime(-2))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", DAILY_INVENTORY_HISTORY);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "库存调整": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getMonthFirstDayByDate(new Date(), -1))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getMonthLastDayByDate(new Date(), -1))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", INVENTORY_RECONCILIATION);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "库存仓储超量费": {
                String taskStartTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getMonthFirstDayByDate(new Date(), -1))));
                String taskEndTimes = AmazonMwsUntil.urlEncode(DateUtil.timeToISO8601ZeroTimeZone((DateUtil.getMonthLastDayByDate(new Date(), -1))));
                params.put("StartDate", taskStartTimes);
                params.put("EndDate", taskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", INVENTORY_STORAGE_OVERAGE_FEES);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "在售商品报告": {
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", ACTIVE_LISTINGS_REPORT);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            default: {
            }
        }
        return requestReportIdAndParamMap;
    }


    /**
     * 发送RequestReport请求获取ReportRequestId 获取历史数据
     *
     * @param taskName
     * @param area
     * @param secretKey
     * @param params
     * @return
     * @throws Exception
     */
    public Map<String, Map<String, String>> sendRequestHistoryReport(String taskName, Area area, String secretKey, Map<String, String> params, String historyTaskStartTimes, String historyTaskEndTimes) throws Exception {
        Map<String, Map<String, String>> requestReportIdAndParamMap = null;
        switch (taskName) {
            case "所有订单": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", ALL_ORDER_ENUMERATION);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "盘库": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", INVENTORY_ADJUSTMENTS);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "已接收库存": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", RECEIVED_INVENTORY);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "每月库存记录": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", MONTHLY_INVENTORY_HISTORY);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "月储存费用": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", MONTHLY_STORAGE_FEES);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "长期仓储费": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", LONG_TERM_STORAGE_FEE_CHARGES);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "退货": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", FBA_CUSTOMER_RETURNS);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "移除订单详情": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", REMOVAL_ORDER_DETAIL);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "移除货件详情": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", REMOVAL_SHIPMENT_DETAIL);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "已完成订单": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", CUSTOMER_SHIPMENT_SALES);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "赔偿": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", REIMBURSEMENTS);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "每日库存记录": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", DAILY_INVENTORY_HISTORY);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "库存调整": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", INVENTORY_RECONCILIATION);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            case "库存仓储超量费": {
                params.put("StartDate", historyTaskStartTimes);
                params.put("EndDate", historyTaskEndTimes);
                params.put("SecretKey", secretKey);
                params.put("mwsUrl", area.getMwsEndPoint());
                params.put("ReportType", INVENTORY_STORAGE_OVERAGE_FEES);
                requestReportIdAndParamMap = handleRequestReport(params);
            }
            break;
            default: {
            }
        }
        return requestReportIdAndParamMap;
    }


    /**
     * payment 将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handlePayment(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByName(shopName);
        String sellerId = shop.getSellerId();
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        int settlementIdIndex = Integer.valueOf(String.valueOf(titleMap.get("settlement-id")));
        int settlementStartDateIndex = Integer.valueOf(String.valueOf(titleMap.get("settlement-start-date")));
        int settlementEndDateIndex = Integer.valueOf(String.valueOf(titleMap.get("settlement-end-date")));
        int depositDateIndex = Integer.valueOf(String.valueOf(titleMap.get("deposit-date")));
        int totalAmountIndex = Integer.valueOf(String.valueOf(titleMap.get("total-amount")));
        int currencyIndex = Integer.valueOf(String.valueOf(titleMap.get("currency")));
        int transactionTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("transaction-type")));
        int orderIdIndex = Integer.valueOf(String.valueOf(titleMap.get("order-id")));
        int merchantOrderIdIndex = Integer.valueOf(String.valueOf(titleMap.get("merchant-order-id")));
        int adjustmentIdIndex = Integer.valueOf(String.valueOf(titleMap.get("adjustment-id")));
        int shipmentIdIndex = Integer.valueOf(String.valueOf(titleMap.get("shipment-id")));
        int marketplaceNameIndex = Integer.valueOf(String.valueOf(titleMap.get("marketplace-name")));
        int shipmentFeeTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("shipment-fee-type")));
        int shipmentFeeAmountIndex = Integer.valueOf(String.valueOf(titleMap.get("shipment-fee-amount")));
        int orderFeeTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("order-fee-type")));
        int orderFeeAmountIndex = Integer.valueOf(String.valueOf(titleMap.get("order-fee-amount")));
        int fulfillmentIdIndex = Integer.valueOf(String.valueOf(titleMap.get("fulfillment-id")));
        int postedDateIndex = Integer.valueOf(String.valueOf(titleMap.get("posted-date")));
        int orderItemCodeIndex = Integer.valueOf(String.valueOf(titleMap.get("order-item-code")));
        int merchantOrderItemIdIndex = Integer.valueOf(String.valueOf(titleMap.get("merchant-order-item-id")));
        int merchantAdjustmentItemIdIndex = Integer.valueOf(String.valueOf(titleMap.get("merchant-adjustment-item-id")));
        int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
        int quantityPurchasedIndex = Integer.valueOf(String.valueOf(titleMap.get("quantity-purchased")));
        int priceTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("price-type")));
        int priceAmountIndex = Integer.valueOf(String.valueOf(titleMap.get("price-amount")));
        int itemRelatedFeeTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("item-related-fee-type")));
        int itemRelatedFeeAmountIndex = Integer.valueOf(String.valueOf(titleMap.get("item-related-fee-amount")));
        int miscFeeAmountIndex = Integer.valueOf(String.valueOf(titleMap.get("misc-fee-amount")));
        int otherFeeAmountIndex = Integer.valueOf(String.valueOf(titleMap.get("other-fee-amount")));
        int otherFeeReasonDescriptionIndex = Integer.valueOf(String.valueOf(titleMap.get("other-fee-reason-description")));
        int promotionIdIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("promotion-id")) {
            promotionIdIndex = Integer.valueOf(String.valueOf(titleMap.get("promotion-id")));
        }
        int promotionTypeIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("promotion-type")) {
            promotionTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("promotion-type")));
        }
        int promotionAmountIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("promotion-amount")) {
            promotionAmountIndex = Integer.valueOf(String.valueOf(titleMap.get("promotion-amount")));
        }
        int directPaymentTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("direct-payment-type")));
        int directPaymentAmountIndex = Integer.valueOf(String.valueOf(titleMap.get("direct-payment-amount")));
        int otherAmountIndex = Integer.valueOf(String.valueOf(titleMap.get("other-amount")));

        List<Object> insertList = new ArrayList<>(lists.size());
        for (int i = 1; i < lists.size(); i++) {
            MwsPayment mwsPayment = new MwsPayment();
            List<String> row = lists.get(i);
            mwsPayment.setSellerId(sellerId);
            mwsPayment.setArea(area);
            mwsPayment.setSettlementId(row.get(settlementIdIndex));
            mwsPayment.setSettlementStartDate(DateUtil.ISO8601ToGMT(row.get(settlementStartDateIndex)));
            mwsPayment.setSettlementEndDate(DateUtil.ISO8601ToGMT(row.get(settlementEndDateIndex)));
            mwsPayment.setDepositDate(DateUtil.ISO8601ToGMT(row.get(depositDateIndex)));
            mwsPayment.setTotalAmount(ToolUtil.valueOfDouble(row.get(totalAmountIndex)));
            mwsPayment.setCurrency(row.get(currencyIndex));
            mwsPayment.setTransactionType(row.get(transactionTypeIndex));
            mwsPayment.setOrderId(row.get(orderIdIndex));
            mwsPayment.setMerchantOrderId(row.get(merchantOrderIdIndex));
            mwsPayment.setAdjustmentId(row.get(adjustmentIdIndex));
            mwsPayment.setShipmentId(row.get(shipmentIdIndex));
            mwsPayment.setMarketplaceName(row.get(marketplaceNameIndex));
            mwsPayment.setShipmentFeeType(row.get(shipmentFeeTypeIndex));
            mwsPayment.setShipmentFeeAmount(ToolUtil.valueOfDouble(row.get(shipmentFeeAmountIndex)));
            mwsPayment.setOrderFeeType(row.get(orderFeeTypeIndex));
            mwsPayment.setOrderFeeAmount(row.get(orderFeeAmountIndex));
            mwsPayment.setFulfillmentId(row.get(fulfillmentIdIndex));
            mwsPayment.setPostedDate(DateUtil.ISO8601ToGMT(row.get(postedDateIndex)));
            mwsPayment.setOrderItemCode(row.get(orderItemCodeIndex));
            mwsPayment.setMerchantOrderItemId(row.get(merchantOrderItemIdIndex));
            mwsPayment.setMerchantAdjustmentItemId(row.get(merchantAdjustmentItemIdIndex));
            mwsPayment.setSku(row.get(skuIndex));
            mwsPayment.setQuantityPurchased(ToolUtil.valueOfInteger(row.get(quantityPurchasedIndex)));
            mwsPayment.setPriceType(row.get(priceTypeIndex));
            mwsPayment.setPriceAmount(ToolUtil.valueOfDouble(row.get(priceAmountIndex)));
            mwsPayment.setItemRelatedFeeType(row.get(itemRelatedFeeTypeIndex));
            mwsPayment.setItemRelatedFeeAmount(ToolUtil.valueOfDouble(row.get(itemRelatedFeeAmountIndex)));
            mwsPayment.setMiscFeeAmount(ToolUtil.valueOfDouble(row.get(miscFeeAmountIndex)));
            mwsPayment.setOtherFeeAmount(ToolUtil.valueOfDouble(row.get(otherFeeAmountIndex)));
            mwsPayment.setOtherFeeReasonDescription(row.get(otherFeeReasonDescriptionIndex));
            if (promotionIdIndex != WRONG_NUMBER_SIGN) {
                mwsPayment.setPromotionId(row.get(promotionIdIndex));
            }
            if (promotionTypeIndex != WRONG_NUMBER_SIGN) {
                mwsPayment.setPromotionType(row.get(promotionTypeIndex));
            }
            if (promotionAmountIndex != WRONG_NUMBER_SIGN) {
                mwsPayment.setPromotionAmount(ToolUtil.valueOfDouble(row.get(promotionAmountIndex)));
            }
            mwsPayment.setDirectPaymentType(row.get(directPaymentTypeIndex));
            mwsPayment.setDirectPaymentAmount(ToolUtil.valueOfDouble(row.get(directPaymentAmountIndex)));
            mwsPayment.setOtherAmount(ToolUtil.valueOfDouble(row.get(otherAmountIndex)));
            mwsPayment.setCreateTime(DateUtil.getDate());
            mwsPayment.setUpdateTime(DateUtil.getDate());
            insertList.add(mwsPayment);
        }
        return insertList;
    }

    /**
     * 在售商品报告  将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleActiveListingsReport(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        // 原数据插入数据库的集合
        List<Object> insertList = new ArrayList<>(lists.size());
        // 原数据处理集合
        List<MwsActiveListingsReport> mwsActiveListingsReportList = new ArrayList<>(lists.size());
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        // 不用区域的表头有所不同，目前是日本区域的不同需要做额外处理
        if (null != area && area.equals(ESPECIALLY_AREA) && !shopName.equals(SPECIAL_SHOP_NAME)) {
            int itemNameIndex = Integer.valueOf(String.valueOf(titleMap.get("商品名")));
            int listingIdIndex = Integer.valueOf(String.valueOf(titleMap.get("出品ID")));
            int sellerSkuIndex = Integer.valueOf(String.valueOf(titleMap.get("出品者SKU")));
            int priceIndex = Integer.valueOf(String.valueOf(titleMap.get("価格")));
            int quantityIndex = Integer.valueOf(String.valueOf(titleMap.get("数量")));
            int openDateIndex = Integer.valueOf(String.valueOf(titleMap.get("出品日")));
            int productIdTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("商品IDタイプ")));
            int itemNoteIndex = Integer.valueOf(String.valueOf(titleMap.get("コンディション説明")));
            int itemConditionIndex = Integer.valueOf(String.valueOf(titleMap.get("コンディション")));
            int willShipInternationallyIndex = Integer.valueOf(String.valueOf(titleMap.get("国外へ配送可")));
            int expeditedShippingIndex = Integer.valueOf(String.valueOf(titleMap.get("迅速な配送")));
            int productIdIndex = Integer.valueOf(String.valueOf(titleMap.get("商品ID")));
            int pendingQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("在庫数")));
            int fulfillmentChannelIndex = Integer.valueOf(String.valueOf(titleMap.get("フルフィルメント・チャンネル")));
            int statusIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("ステータス")) {
                statusIndex = Integer.valueOf(String.valueOf(titleMap.get("ステータス")));
            }
            int merchantShippingGroupIndex = Integer.valueOf(String.valueOf(titleMap.get("merchant-shipping-group")));

            for (int i = 1; i < lists.size(); i++) {
                MwsActiveListingsReport mwsActiveListingsReport = new MwsActiveListingsReport();
                List<String> row = lists.get(i);
                mwsActiveListingsReport.setSellerId(sellerId);
                mwsActiveListingsReport.setArea(area);
                mwsActiveListingsReport.setItemName(row.get(itemNameIndex));
                mwsActiveListingsReport.setListingId(row.get(listingIdIndex));
                mwsActiveListingsReport.setSellerSku(row.get(sellerSkuIndex));
                mwsActiveListingsReport.setPrice(ToolUtil.valueOfDouble(row.get(priceIndex)));
                mwsActiveListingsReport.setQuantity(ToolUtil.valueOfInteger(row.get(quantityIndex)));
                if (row.get(openDateIndex).contains("/")) {
                    mwsActiveListingsReport.setOpenDate(DateUtil.ISO8601ToGMTByOtherPattern(row.get(openDateIndex)));
                } else {
                    mwsActiveListingsReport.setOpenDate(DateUtil.ISO8601ToGMTByOtherPatternTwo(row.get(openDateIndex)));
                }
                mwsActiveListingsReport.setProductIdType(row.get(productIdTypeIndex));
                mwsActiveListingsReport.setItemNote(row.get(itemNoteIndex));
                mwsActiveListingsReport.setItemCondition(row.get(itemConditionIndex));
                mwsActiveListingsReport.setWillShipInternationally(row.get(willShipInternationallyIndex));
                mwsActiveListingsReport.setExpeditedShipping(row.get(expeditedShippingIndex));
                mwsActiveListingsReport.setProductId(row.get(productIdIndex));
                mwsActiveListingsReport.setPendingQuantity(ToolUtil.valueOfInteger(row.get(pendingQuantityIndex)));
                mwsActiveListingsReport.setFulfillmentChannel(row.get(fulfillmentChannelIndex));
                mwsActiveListingsReport.setMerchantShippingGroup(row.get(merchantShippingGroupIndex));
                mwsActiveListingsReport.setCreateTime(DateUtil.getDate());
                mwsActiveListingsReport.setUpdateTime(DateUtil.getDate());
                if (statusIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setStatus(row.get(statusIndex));
                }
                mwsActiveListingsReport.setFinanceStatus(1);
                insertList.add(mwsActiveListingsReport);
                mwsActiveListingsReportList.add(mwsActiveListingsReport);
            }
            return insertList;
        } else {
            int itemNameIndex = Integer.valueOf(String.valueOf(titleMap.get("item-name")));
            int itemDescriptionIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("item-description")) {
                itemDescriptionIndex = Integer.valueOf(String.valueOf(titleMap.get("item-description")));
            }
            int listingIdIndex = Integer.valueOf(String.valueOf(titleMap.get("listing-id")));
            int sellerSkuIndex = Integer.valueOf(String.valueOf(titleMap.get("seller-sku")));
            int priceIndex = Integer.valueOf(String.valueOf(titleMap.get("price")));
            int quantityIndex = Integer.valueOf(String.valueOf(titleMap.get("quantity")));
            int openDateIndex = Integer.valueOf(String.valueOf(titleMap.get("open-date")));
            int imageUrlIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("image-url")) {
                imageUrlIndex = Integer.valueOf(String.valueOf(titleMap.get("image-url")));
            }
            int itemIsMarketplaceIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("item-is-marketplace")) {
                itemIsMarketplaceIndex = Integer.valueOf(String.valueOf(titleMap.get("item-is-marketplace")));
            }
            int productIdTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("product-id-type")));
            int zshopShippingFeeIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("zshop-shipping-fee")) {
                zshopShippingFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("zshop-shipping-fee")));
            }
            int itemNoteIndex = Integer.valueOf(String.valueOf(titleMap.get("item-note")));
            int itemConditionIndex = Integer.valueOf(String.valueOf(titleMap.get("item-condition")));
            int zshopCategory1Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("zshop-category1")) {
                zshopCategory1Index = Integer.valueOf(String.valueOf(titleMap.get("zshop-category1")));
            }
            int zshopBrowsePathIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("zshop-browse-path")) {
                zshopBrowsePathIndex = Integer.valueOf(String.valueOf(titleMap.get("zshop-browse-path")));
            }
            int zshopStorefrontFeatureIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("zshop-storefront-feature")) {
                zshopStorefrontFeatureIndex = Integer.valueOf(String.valueOf(titleMap.get("zshop-storefront-feature")));
            }
            int asin1Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("asin1")) {
                asin1Index = Integer.valueOf(String.valueOf(titleMap.get("asin1")));
            }
            int asin2Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("asin2")) {
                asin2Index = Integer.valueOf(String.valueOf(titleMap.get("asin2")));
            }
            int asin3Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("asin3")) {
                asin3Index = Integer.valueOf(String.valueOf(titleMap.get("asin3")));
            }

            int willShipInternationallyIndex = Integer.valueOf(String.valueOf(titleMap.get("will-ship-internationally")));
            int expeditedShippingIndex = Integer.valueOf(String.valueOf(titleMap.get("expedited-shipping")));
            int zshopBoldfaceIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("zshop-boldface")) {
                zshopBoldfaceIndex = Integer.valueOf(String.valueOf(titleMap.get("zshop-boldface")));
            }
            int productIdIndex = Integer.valueOf(String.valueOf(titleMap.get("product-id")));
            int bidForFeaturedPlacementIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("bid-for-featured-placement")) {
                bidForFeaturedPlacementIndex = Integer.valueOf(String.valueOf(titleMap.get("bid-for-featured-placement")));
            }
            int addDeleteIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("add-delete")) {
                addDeleteIndex = Integer.valueOf(String.valueOf(titleMap.get("add-delete")));
            }
            int pendingQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("pending-quantity")));
            int fulfillmentChannelIndex = Integer.valueOf(String.valueOf(titleMap.get("fulfillment-channel")));
            int BusinessPriceIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("Business Price")) {
                BusinessPriceIndex = Integer.valueOf(String.valueOf(titleMap.get("Business Price")));
            }
            int QuantityPriceTypeIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("Quantity Price Type")) {
                QuantityPriceTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("Quantity Price Type")));
            }
            int QuantityLowerBound1Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("Quantity Lower Bound 1")) {
                QuantityLowerBound1Index = Integer.valueOf(String.valueOf(titleMap.get("Quantity Lower Bound 1")));
            }
            int QuantityPrice1Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("Quantity Price 1")) {
                QuantityPrice1Index = Integer.valueOf(String.valueOf(titleMap.get("Quantity Price 1")));
            }
            int QuantityLowerBound2Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("Quantity Lower Bound 2")) {
                QuantityLowerBound2Index = Integer.valueOf(String.valueOf(titleMap.get("Quantity Lower Bound 2")));
            }
            int QuantityPrice2Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("Quantity Price 2")) {
                QuantityPrice2Index = Integer.valueOf(String.valueOf(titleMap.get("Quantity Price 2")));
            }
            int QuantityLowerBound3Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("Quantity Lower Bound 3")) {
                QuantityLowerBound3Index = Integer.valueOf(String.valueOf(titleMap.get("Quantity Lower Bound 3")));
            }
            int QuantityPrice3Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("Quantity Price 3")) {
                QuantityPrice3Index = Integer.valueOf(String.valueOf(titleMap.get("Quantity Price 3")));
            }
            int QuantityLowerBound4Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("Quantity Lower Bound 4")) {
                QuantityLowerBound4Index = Integer.valueOf(String.valueOf(titleMap.get("Quantity Lower Bound 4")));
            }
            int QuantityPrice4Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("Quantity Price 4")) {
                QuantityPrice4Index = Integer.valueOf(String.valueOf(titleMap.get("Quantity Price 4")));
            }
            int QuantityLowerBound5Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("Quantity Lower Bound 5")) {
                QuantityLowerBound5Index = Integer.valueOf(String.valueOf(titleMap.get("Quantity Lower Bound 5")));
            }
            int QuantityPrice5Index = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("Quantity Price 5")) {
                QuantityPrice5Index = Integer.valueOf(String.valueOf(titleMap.get("Quantity Price 5")));
            }
            int statusIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("status")) {
                statusIndex = Integer.valueOf(String.valueOf(titleMap.get("status")));
            }
            int merchantShippingGroupIndex = WRONG_NUMBER_SIGN;
            if (null != titleMap.get("merchant-shipping-group")) {
                merchantShippingGroupIndex = Integer.valueOf(String.valueOf(titleMap.get("merchant-shipping-group")));
            }
            for (int i = 1; i < lists.size(); i++) {
                MwsActiveListingsReport mwsActiveListingsReport = new MwsActiveListingsReport();
                List<String> row = lists.get(i);
                mwsActiveListingsReport.setSellerId(sellerId);
                mwsActiveListingsReport.setArea(area);
                mwsActiveListingsReport.setItemName(row.get(itemNameIndex));
                if (itemDescriptionIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setItemDescription(row.get(itemDescriptionIndex));
                }
                mwsActiveListingsReport.setListingId(row.get(listingIdIndex));
                mwsActiveListingsReport.setSellerSku(row.get(sellerSkuIndex));
                mwsActiveListingsReport.setPrice(ToolUtil.valueOfDouble(row.get(priceIndex)));
                mwsActiveListingsReport.setQuantity(ToolUtil.valueOfInteger(row.get(quantityIndex)));
                if (row.get(openDateIndex).contains("/")) {
                    mwsActiveListingsReport.setOpenDate(DateUtil.ISO8601ToGMTByOtherPattern(row.get(openDateIndex)));
                } else {
                    mwsActiveListingsReport.setOpenDate(DateUtil.ISO8601ToGMTByOtherPatternTwo(row.get(openDateIndex)));
                }
                if (imageUrlIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setImageUrl(row.get(imageUrlIndex));
                }
                if (itemIsMarketplaceIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setItemIsMarketplace(row.get(itemIsMarketplaceIndex));
                }
                mwsActiveListingsReport.setProductIdType(row.get(productIdTypeIndex));
                if (zshopShippingFeeIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setZshopShippingFee(row.get(zshopShippingFeeIndex));
                }
                mwsActiveListingsReport.setItemNote(row.get(itemNoteIndex));
                mwsActiveListingsReport.setItemCondition(row.get(itemConditionIndex));
                if (zshopCategory1Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setZshopCategory1(row.get(zshopCategory1Index));
                }
                if (zshopBrowsePathIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setZshopBrowsePath(row.get(zshopBrowsePathIndex));
                }
                if (zshopStorefrontFeatureIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setZshopStorefrontFeature(row.get(zshopStorefrontFeatureIndex));
                }
                if (asin1Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setAsin1(row.get(asin1Index));
                }
                if (asin2Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setAsin2(row.get(asin2Index));
                }
                if (asin3Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setAsin3(row.get(asin3Index));
                }
                mwsActiveListingsReport.setWillShipInternationally(row.get(willShipInternationallyIndex));
                mwsActiveListingsReport.setExpeditedShipping(row.get(expeditedShippingIndex));
                if (zshopBoldfaceIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setZshopBoldface(row.get(zshopBoldfaceIndex));
                }
                mwsActiveListingsReport.setProductId(row.get(productIdIndex));
                if (bidForFeaturedPlacementIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setBidForFeaturedPlacement(row.get(bidForFeaturedPlacementIndex));
                }
                if (addDeleteIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setAddDelete(row.get(addDeleteIndex));
                }
                mwsActiveListingsReport.setPendingQuantity(ToolUtil.valueOfInteger(row.get(pendingQuantityIndex)));
                mwsActiveListingsReport.setFulfillmentChannel(row.get(fulfillmentChannelIndex));
                if (merchantShippingGroupIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setMerchantShippingGroup(row.get(merchantShippingGroupIndex));
                }
                if (BusinessPriceIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setBusinessPrice(ToolUtil.valueOfDouble(row.get(BusinessPriceIndex)));
                }
                if (QuantityPriceTypeIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setQuantityPriceType(row.get(QuantityPriceTypeIndex));
                }
                if (QuantityLowerBound1Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setQuantityLowerBound1(ToolUtil.valueOfInteger(row.get(QuantityLowerBound1Index)));
                }
                if (QuantityPrice1Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setQuantityPrice1(ToolUtil.valueOfDouble(row.get(QuantityPrice1Index)));
                }
                if (QuantityLowerBound2Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setQuantityLowerBound2(ToolUtil.valueOfInteger(row.get(QuantityLowerBound2Index)));
                }
                if (QuantityPrice2Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setQuantityPrice2(ToolUtil.valueOfDouble(row.get(QuantityPrice2Index)));
                }
                if (QuantityLowerBound3Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setQuantityLowerBound3(ToolUtil.valueOfInteger(row.get(QuantityLowerBound2Index)));
                }
                if (QuantityPrice3Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setQuantityPrice3(ToolUtil.valueOfDouble(row.get(QuantityPrice2Index)));
                }
                if (QuantityLowerBound4Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setQuantityLowerBound4(ToolUtil.valueOfInteger(row.get(QuantityLowerBound4Index)));
                }
                if (QuantityPrice4Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setQuantityPrice4(ToolUtil.valueOfDouble(row.get(QuantityPrice4Index)));
                }
                if (QuantityLowerBound5Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setQuantityLowerBound5(ToolUtil.valueOfInteger(row.get(QuantityLowerBound5Index)));
                }
                if (QuantityPrice5Index != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setQuantityPrice5(ToolUtil.valueOfDouble(row.get(QuantityPrice5Index)));
                }
                if (statusIndex != WRONG_NUMBER_SIGN) {
                    mwsActiveListingsReport.setStatus(row.get(statusIndex));
                }
                mwsActiveListingsReport.setCreateTime(DateUtil.getDate());
                mwsActiveListingsReport.setUpdateTime(DateUtil.getDate());
                mwsActiveListingsReport.setFinanceStatus(1);
                insertList.add(mwsActiveListingsReport);
                mwsActiveListingsReportList.add(mwsActiveListingsReport);
            }
            return insertList;
        }
    }


    /**
     * 库存仓储超量费  将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleInventoryStorageOverageFees(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<Object> insertList = new ArrayList<>(lists.size());
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        int chargedDateIndex = Integer.valueOf(String.valueOf(titleMap.get("charged_date")));
        int storageTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("storage_type")));
        int chargeRateIndex = Integer.valueOf(String.valueOf(titleMap.get("charge_rate")));
        int storageUsageVolumeIndex = Integer.valueOf(String.valueOf(titleMap.get("storage_usage_volume")));
        int storageLimitVolumeIndex = Integer.valueOf(String.valueOf(titleMap.get("storage_limit_volume")));
        int overageVolumeIndex = Integer.valueOf(String.valueOf(titleMap.get("overage_volume")));
        int volumeUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("volume_unit")));
        int chargedFeeAmountIndex = Integer.valueOf(String.valueOf(titleMap.get("charged_fee_amount")));
        int currencyCodeIndex = Integer.valueOf(String.valueOf(titleMap.get("currency_code")));
        for (int i = 1; i < lists.size(); i++) {
            MwsInventoryStorageOverageFees mwsInventoryStorageOverageFees = new MwsInventoryStorageOverageFees();
            List<String> row = lists.get(i);
            mwsInventoryStorageOverageFees.setSellerId(sellerId);
            mwsInventoryStorageOverageFees.setArea(area);
            mwsInventoryStorageOverageFees.setChargedDate(DateUtil.ISO8601ToGMT(row.get(chargedDateIndex)));
            mwsInventoryStorageOverageFees.setStorageType(row.get(storageTypeIndex));
            mwsInventoryStorageOverageFees.setChargeRate(ToolUtil.valueOfLong(row.get(chargeRateIndex)));
            mwsInventoryStorageOverageFees.setStorageUsageVolume(ToolUtil.valueOfDouble(row.get(storageUsageVolumeIndex)));
            mwsInventoryStorageOverageFees.setStorageLimitVolume(ToolUtil.valueOfLong(row.get(storageLimitVolumeIndex)));
            mwsInventoryStorageOverageFees.setOverageVolume(ToolUtil.valueOfDouble(row.get(overageVolumeIndex)));
            mwsInventoryStorageOverageFees.setVolumeUnit(row.get(volumeUnitIndex));
            mwsInventoryStorageOverageFees.setChargedFeeAm(ToolUtil.valueOfDouble(row.get(chargedFeeAmountIndex)));
            mwsInventoryStorageOverageFees.setCurrencyCode(row.get(currencyCodeIndex));
            mwsInventoryStorageOverageFees.setCreateTime(DateUtil.getDate());
            mwsInventoryStorageOverageFees.setUpdateTime(DateUtil.getDate());
            mwsInventoryStorageOverageFees.setFinanceStatus(1);
            insertList.add(mwsInventoryStorageOverageFees);
        }
        return insertList;
    }


    /**
     * 库存调整  将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleInventoryReconciliation(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<Object> insertList = new ArrayList<>(lists.size());
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        // 不用区域的表头有所不同，目前是日本区域的不同需要做额外处理
        if (null != area && area.equals(ESPECIALLY_AREA)) {
            int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("Fulfillment Network SKU")));
            int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("Merchant SKU")));
            int asinIndex = Integer.valueOf(String.valueOf(titleMap.get("ASIN")));
            int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("Title")));
            int conditionIndex = Integer.valueOf(String.valueOf(titleMap.get("Condition")));
            int beginningQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("beginning-quantity")));
            int endingQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("ending-quantity")));
            int receivedIndex = Integer.valueOf(String.valueOf(titleMap.get("Received")));
            int returnedIndex = Integer.valueOf(String.valueOf(titleMap.get("FBA customer returns")));
            int foundIndex = Integer.valueOf(String.valueOf(titleMap.get("found")));
            int soldIndex = Integer.valueOf(String.valueOf(titleMap.get("sold")));
            int removedIndex = Integer.valueOf(String.valueOf(titleMap.get("removed")));
            int lostIndex = Integer.valueOf(String.valueOf(titleMap.get("lost")));
            int disposedIndex = Integer.valueOf(String.valueOf(titleMap.get("Inventory disposed of")));
            int otherIndex = Integer.valueOf(String.valueOf(titleMap.get("Other")));
            int discrepantIndex = Integer.valueOf(String.valueOf(titleMap.get("discrepant-quantity")));
            for (int i = 1; i < lists.size(); i++) {
                MwsInventoryReconciliation mwsInventoryReconciliation = new MwsInventoryReconciliation();
                List<String> row = lists.get(i);
                mwsInventoryReconciliation.setSellerId(sellerId);
                mwsInventoryReconciliation.setArea(area);
                mwsInventoryReconciliation.setAsin(row.get(asinIndex));
                mwsInventoryReconciliation.setFnsku(row.get(fnskuIndex));
                mwsInventoryReconciliation.setSku(row.get(skuIndex));
                mwsInventoryReconciliation.setProductName(row.get(productNameIndex));
                mwsInventoryReconciliation.setCondition(row.get(conditionIndex));
                mwsInventoryReconciliation.setBeginningQuantity(ToolUtil.valueOfInteger(row.get(beginningQuantityIndex)));
                mwsInventoryReconciliation.setEndingQuantity(ToolUtil.valueOfInteger(row.get(endingQuantityIndex)));
                mwsInventoryReconciliation.setReceived(ToolUtil.valueOfInteger(row.get(receivedIndex)));
                mwsInventoryReconciliation.setReturned(ToolUtil.valueOfInteger(row.get(returnedIndex)));
                mwsInventoryReconciliation.setFound(ToolUtil.valueOfInteger(row.get(foundIndex)));
                mwsInventoryReconciliation.setSold(ToolUtil.valueOfInteger(row.get(soldIndex)));
                mwsInventoryReconciliation.setRemoved(ToolUtil.valueOfInteger(row.get(removedIndex)));
                mwsInventoryReconciliation.setLost(ToolUtil.valueOfInteger(row.get(lostIndex)));
                mwsInventoryReconciliation.setDisposed(ToolUtil.valueOfInteger(row.get(disposedIndex)));
                mwsInventoryReconciliation.setOther(ToolUtil.valueOfInteger(row.get(otherIndex)));
                mwsInventoryReconciliation.setDiscrepantQuantity(ToolUtil.valueOfInteger(row.get(discrepantIndex)));
                mwsInventoryReconciliation.setCreateTime(DateUtil.getDate());
                mwsInventoryReconciliation.setUpdateTime(DateUtil.getDate());
                mwsInventoryReconciliation.setFinanceStatus(1);
                insertList.add(mwsInventoryReconciliation);
            }
            return insertList;
        } else {
            int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
            int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
            int asinIndex = Integer.valueOf(String.valueOf(titleMap.get("asin")));
            int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("product-name")));
            int conditionIndex = Integer.valueOf(String.valueOf(titleMap.get("condition")));
            int beginningQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("beginning-quantity")));
            int endingQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("ending-quantity")));
            int receivedIndex = Integer.valueOf(String.valueOf(titleMap.get("received")));
            int returnedIndex = Integer.valueOf(String.valueOf(titleMap.get("returned")));
            int foundIndex = Integer.valueOf(String.valueOf(titleMap.get("found")));
            int soldIndex = Integer.valueOf(String.valueOf(titleMap.get("sold")));
            int removedIndex = Integer.valueOf(String.valueOf(titleMap.get("removed")));
            int lostIndex = Integer.valueOf(String.valueOf(titleMap.get("lost")));
            int disposedIndex = Integer.valueOf(String.valueOf(titleMap.get("disposed")));
            int otherIndex = Integer.valueOf(String.valueOf(titleMap.get("other")));
            int discrepantIndex = Integer.valueOf(String.valueOf(titleMap.get("discrepant-quantity")));
            for (int i = 1; i < lists.size(); i++) {
                MwsInventoryReconciliation mwsInventoryReconciliation = new MwsInventoryReconciliation();
                List<String> row = lists.get(i);
                mwsInventoryReconciliation.setSellerId(sellerId);
                mwsInventoryReconciliation.setArea(area);
                mwsInventoryReconciliation.setAsin(row.get(asinIndex));
                mwsInventoryReconciliation.setFnsku(row.get(fnskuIndex));
                mwsInventoryReconciliation.setSku(row.get(skuIndex));
                mwsInventoryReconciliation.setProductName(row.get(productNameIndex));
                mwsInventoryReconciliation.setCondition(row.get(conditionIndex));
                mwsInventoryReconciliation.setBeginningQuantity(ToolUtil.valueOfInteger(row.get(beginningQuantityIndex)));
                mwsInventoryReconciliation.setEndingQuantity(ToolUtil.valueOfInteger(row.get(endingQuantityIndex)));
                mwsInventoryReconciliation.setReceived(ToolUtil.valueOfInteger(row.get(receivedIndex)));
                mwsInventoryReconciliation.setReturned(ToolUtil.valueOfInteger(row.get(returnedIndex)));
                mwsInventoryReconciliation.setFound(ToolUtil.valueOfInteger(row.get(foundIndex)));
                mwsInventoryReconciliation.setSold(ToolUtil.valueOfInteger(row.get(soldIndex)));
                mwsInventoryReconciliation.setRemoved(ToolUtil.valueOfInteger(row.get(removedIndex)));
                mwsInventoryReconciliation.setLost(ToolUtil.valueOfInteger(row.get(lostIndex)));
                mwsInventoryReconciliation.setDisposed(ToolUtil.valueOfInteger(row.get(disposedIndex)));
                mwsInventoryReconciliation.setOther(ToolUtil.valueOfInteger(row.get(otherIndex)));
                mwsInventoryReconciliation.setDiscrepantQuantity(ToolUtil.valueOfInteger(row.get(discrepantIndex)));
                mwsInventoryReconciliation.setCreateTime(DateUtil.getDate());
                mwsInventoryReconciliation.setUpdateTime(DateUtil.getDate());
                mwsInventoryReconciliation.setFinanceStatus(1);
                insertList.add(mwsInventoryReconciliation);
            }
            return insertList;
        }
    }


    /**
     * 每日库存记录 将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleEveryDayInventoryRecords(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        if (null != area && area.equals(ESPECIALLY_AREA) && !shopName.equals(SPECIAL_SHOP_NAME) && !shopName.equals(SPECIAL_SHOP_NAME_TWO)) {
            int snapshotDateIndex = Integer.valueOf(String.valueOf(titleMap.get("Date")));
//            //获取数据库里面最新的日期
//            String latestDay = mwsDailyInventoryHistoryMapper.getLatestDay(sellerId, area);
//            if (!StringUtil.isBlank(latestDay)) {
//                // 因为获取的时间都是一样的，所以只要拿一个进行比较就行
//                List<String> firstList = lists.get(1);
//                //如果当前获取的数据的时间和数据库时间一致或者早于数据库时间，本次获取数据将跳过
//                if (DateUtil.compateTime(latestDay, DateUtil.ISO8601ToGMT(firstList.get(snapshotDateIndex))) >= 0) {
//                    return null;
//                }
//            }
            int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("FNSKU")));
            int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("Merchant SKU")));
            int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("Title")));
            int quantityIndex = Integer.valueOf(String.valueOf(titleMap.get("Quantity")));
            int fulfillmentCenterIdIndex = Integer.valueOf(String.valueOf(titleMap.get("FC")));
            int detailedDispositionIndex = Integer.valueOf(String.valueOf(titleMap.get("Disposition")));
            int countryIndex = Integer.valueOf(String.valueOf(titleMap.get("Country")));
            List<Object> list = new ArrayList<>(lists.size());
            for (int i = 1; i < lists.size(); i++) {
                MwsDailyInventoryHistory mwsDailyInventoryHistory = new MwsDailyInventoryHistory();
                List<String> stringList = lists.get(i);
                mwsDailyInventoryHistory.setSellerId(sellerId);
                mwsDailyInventoryHistory.setArea(area);
                mwsDailyInventoryHistory.setSnapshotDate(DateUtil.ISO8601ToGMT(stringList.get(snapshotDateIndex)));
                mwsDailyInventoryHistory.setFnsku(stringList.get(fnskuIndex));
                mwsDailyInventoryHistory.setSku(stringList.get(skuIndex));
                mwsDailyInventoryHistory.setProductName(stringList.get(productNameIndex));
                mwsDailyInventoryHistory.setQuantity(ToolUtil.valueOfInteger(stringList.get(quantityIndex)));
                mwsDailyInventoryHistory.setFulfillmentCenterId(stringList.get(fulfillmentCenterIdIndex));
                mwsDailyInventoryHistory.setDetailedDisposition(stringList.get(detailedDispositionIndex));
                mwsDailyInventoryHistory.setCountry(stringList.get(countryIndex));
                mwsDailyInventoryHistory.setCreateTime(DateUtil.getDate());
                mwsDailyInventoryHistory.setUpdateTime(DateUtil.getDate());
                mwsDailyInventoryHistory.setFinanceStatus(1);
                list.add(mwsDailyInventoryHistory);
            }
            return list;
        } else {
            int snapshotDateIndex = Integer.valueOf(String.valueOf(titleMap.get("snapshot-date")));
//            //获取数据库里面最新的日期
//            String latestDay = mwsDailyInventoryHistoryMapper.getLatestDay(sellerId, area);
//            if (!StringUtil.isBlank(latestDay)) {
//                List<String> firstList = lists.get(1);
//                //如果当前获取的数据的时间和数据库时间一致或者早于数据库时间，本次获取数据将跳过
//                if (DateUtil.compateTime(latestDay, DateUtil.ISO8601ToGMT(firstList.get(snapshotDateIndex))) >= 0) {
//                    return null;
//                }
//            }
            int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
            int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
            int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("product-name")));
            int quantityIndex = Integer.valueOf(String.valueOf(titleMap.get("quantity")));
            int fulfillmentCenterIdIndex = Integer.valueOf(String.valueOf(titleMap.get("fulfillment-center-id")));
            int detailedDispositionIndex = Integer.valueOf(String.valueOf(titleMap.get("detailed-disposition")));
            int countryIndex = Integer.valueOf(String.valueOf(titleMap.get("country")));
            List<Object> list = new ArrayList<>(lists.size());
            for (int i = 1; i < lists.size(); i++) {
                MwsDailyInventoryHistory mwsDailyInventoryHistory = new MwsDailyInventoryHistory();
                List<String> stringList = lists.get(i);
                mwsDailyInventoryHistory.setSellerId(sellerId);
                mwsDailyInventoryHistory.setArea(area);
                mwsDailyInventoryHistory.setSnapshotDate(DateUtil.ISO8601ToGMT(stringList.get(snapshotDateIndex)));
                mwsDailyInventoryHistory.setFnsku(stringList.get(fnskuIndex));
                mwsDailyInventoryHistory.setSku(stringList.get(skuIndex));
                mwsDailyInventoryHistory.setProductName(stringList.get(productNameIndex));
                mwsDailyInventoryHistory.setQuantity(ToolUtil.valueOfInteger(stringList.get(quantityIndex)));
                mwsDailyInventoryHistory.setFulfillmentCenterId(stringList.get(fulfillmentCenterIdIndex));
                mwsDailyInventoryHistory.setDetailedDisposition(stringList.get(detailedDispositionIndex));
                mwsDailyInventoryHistory.setCountry(stringList.get(countryIndex));
                mwsDailyInventoryHistory.setCreateTime(DateUtil.getDate());
                mwsDailyInventoryHistory.setUpdateTime(DateUtil.getDate());
                mwsDailyInventoryHistory.setFinanceStatus(1);
                list.add(mwsDailyInventoryHistory);
            }
            return list;
        }
    }

    /**
     * 赔偿 将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleReimbursements(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        int approvalDateIndex = Integer.valueOf(String.valueOf(titleMap.get("approval-date")));
        int reimbursementIdIndex = Integer.valueOf(String.valueOf(titleMap.get("reimbursement-id")));
        int caseIdIndex = Integer.valueOf(String.valueOf(titleMap.get("case-id")));
        int amazonOrderIdIndex = Integer.valueOf(String.valueOf(titleMap.get("amazon-order-id")));
        int reasonIndex = Integer.valueOf(String.valueOf(titleMap.get("reason")));
        int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
        int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
        int asinIndex = Integer.valueOf(String.valueOf(titleMap.get("asin")));
        int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("product-name")));
        int conditionIndex = Integer.valueOf(String.valueOf(titleMap.get("condition")));
        int currencyUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("currency-unit")));
        int amountPerUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("amount-per-unit")));
        int amountTotalIndex = Integer.valueOf(String.valueOf(titleMap.get("amount-total")));
        int quantityReimbursedCashIndex = Integer.valueOf(String.valueOf(titleMap.get("quantity-reimbursed-cash")));
        int quantityReimbursedInventoryIndex = Integer.valueOf(String.valueOf(titleMap.get("quantity-reimbursed-inventory")));
        int quantityReimbursedTotalIndex = Integer.valueOf(String.valueOf(titleMap.get("quantity-reimbursed-total")));
        int originalReimbursementIdIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("original-reimbursement-id")) {
            originalReimbursementIdIndex = Integer.valueOf(String.valueOf(titleMap.get("original-reimbursement-id")));
        }
        int originalReimbursementTypeIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("original-reimbursement-type")) {
            originalReimbursementTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("original-reimbursement-type")));
        }
        List<Object> insertList = new ArrayList<>(lists.size());
        for (int i = 1; i < lists.size(); i++) {
            MwsReimbursements mwsReimbursements = new MwsReimbursements();
            List<String> row = lists.get(i);
            mwsReimbursements.setSellerId(sellerId);
            mwsReimbursements.setArea(area);
            mwsReimbursements.setApprovalDate(DateUtil.ISO8601ToGMT(row.get(approvalDateIndex)));
            mwsReimbursements.setReimbursementId(row.get(reimbursementIdIndex));
            mwsReimbursements.setCaseId(row.get(caseIdIndex));
            mwsReimbursements.setAmazonOrderId(row.get(amazonOrderIdIndex));
            mwsReimbursements.setReason(row.get(reasonIndex));
            mwsReimbursements.setSku(row.get(skuIndex));
            mwsReimbursements.setFnsku(row.get(fnskuIndex));
            mwsReimbursements.setAsin(row.get(asinIndex));
            mwsReimbursements.setProductName(row.get(productNameIndex));
            mwsReimbursements.setCondition(row.get(conditionIndex));
            mwsReimbursements.setCurrencyUnit(row.get(currencyUnitIndex));
            mwsReimbursements.setAmountPerUnit(ToolUtil.valueOfDouble(row.get(amountPerUnitIndex)));
            mwsReimbursements.setAmountTotal(ToolUtil.valueOfDouble(row.get(amountTotalIndex)));
            mwsReimbursements.setQuantityReimbursedCash(ToolUtil.valueOfInteger(row.get(quantityReimbursedCashIndex)));
            mwsReimbursements.setQuantityReimbursedInventory(ToolUtil.valueOfInteger(row.get(quantityReimbursedInventoryIndex)));
            mwsReimbursements.setQuantityReimbursedTotal(ToolUtil.valueOfInteger(row.get(quantityReimbursedTotalIndex)));
            if (originalReimbursementIdIndex != WRONG_NUMBER_SIGN) {
                mwsReimbursements.setOriginalReimbursementId(row.get(originalReimbursementIdIndex));
            }
            if (originalReimbursementTypeIndex != WRONG_NUMBER_SIGN) {
                mwsReimbursements.setOriginalReimbursementType(row.get(originalReimbursementTypeIndex));
            }
            mwsReimbursements.setCreateTime(DateUtil.getDate());
            mwsReimbursements.setUpdateTime(DateUtil.getDate());
            mwsReimbursements.setFinanceStatus(1);
            insertList.add(mwsReimbursements);
        }
        return insertList;
    }


    /**
     * 已完成订单 将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleCompleteOrder(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        if (null != area && area.equals(ESPECIALLY_AREA) && !shopName.equals(SPECIAL_SHOP_NAME_TWO) && !shopName.equals(SPECIAL_SHOP_NAME)) {
            int shipmentDateIndex = Integer.valueOf(String.valueOf(titleMap.get("Customer Shipment Date")));
            int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("Merchant SKU")));
            int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("FNSKU")));
            int asinIndex = Integer.valueOf(String.valueOf(titleMap.get("ASIN")));
            int fulfillmentCenterIdIndex = Integer.valueOf(String.valueOf(titleMap.get("FC")));
            int quantityIndex = Integer.valueOf(String.valueOf(titleMap.get("Quantity")));
            int amazonOrderIdIndex = Integer.valueOf(String.valueOf(titleMap.get("Amazon Order Id")));
            int currencyIndex = Integer.valueOf(String.valueOf(titleMap.get("Currency")));
            int itemPricePerUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("Product Amount")));
            int shippingPriceIndex = Integer.valueOf(String.valueOf(titleMap.get("Shipping Amount")));
            int giftWrapPriceIndex = Integer.valueOf(String.valueOf(titleMap.get("Gift Amount")));
            int shipCityIndex = Integer.valueOf(String.valueOf(titleMap.get("Shipment To City")));
            int shipStateIndex = Integer.valueOf(String.valueOf(titleMap.get("Shipment To State")));
            int shipPostalCodeIndex = Integer.valueOf(String.valueOf(titleMap.get("Shipment To Postal Code")));
            int PointsGrantedIndex = Integer.valueOf(String.valueOf(titleMap.get("Points Granted")));
//            //获取数据库里面最新的日期
//            String latestDay = mwsCustomerShipmentSalesMapper.getLatestDay(sellerId, area);
            List<Object> insertList = new ArrayList<>(lists.size());
            for (int i = 1; i < lists.size(); i++) {
                List<String> list = lists.get(i);
//                if (!StringUtil.isBlank(latestDay)) {
//                    //如果当前获取的数据的时间和数据库时间一致或者早于数据库时间，本次获取数据将跳过
//                    if (DateUtil.compateTime(latestDay, DateUtil.ISO8601ToGMT(list.get(shipmentDateIndex))) >= 0) {
//                        continue;
//                    }
//                }
                MwsCustomerShipmentSales mwsCustomerShipmentSales = new MwsCustomerShipmentSales();
                mwsCustomerShipmentSales.setSellerId(sellerId);
                mwsCustomerShipmentSales.setArea(area);
                //转为西零时区时间
                mwsCustomerShipmentSales.setShipmentDate(DateUtil.ISO8601ToGMT(list.get(shipmentDateIndex)));
                mwsCustomerShipmentSales.setSku(list.get(skuIndex));
                mwsCustomerShipmentSales.setFnsku(list.get(fnskuIndex));
                mwsCustomerShipmentSales.setAsin(list.get(asinIndex));
                mwsCustomerShipmentSales.setFulfillmentCenterId(list.get(fulfillmentCenterIdIndex));
                mwsCustomerShipmentSales.setQuantity(ToolUtil.valueOfInteger(list.get(quantityIndex)));
                mwsCustomerShipmentSales.setAmazonOrderId(list.get(amazonOrderIdIndex));
                mwsCustomerShipmentSales.setCurrency(list.get(currencyIndex));
                mwsCustomerShipmentSales.setItemPricePerUnit(ToolUtil.valueOfDouble(list.get(itemPricePerUnitIndex)));
                mwsCustomerShipmentSales.setShippingPrice(ToolUtil.valueOfDouble(list.get(shippingPriceIndex)));
                mwsCustomerShipmentSales.setGiftWrapPrice(ToolUtil.valueOfDouble(list.get(giftWrapPriceIndex)));
                mwsCustomerShipmentSales.setShipCity(list.get(shipCityIndex));
                mwsCustomerShipmentSales.setShipState(list.get(shipStateIndex));
                mwsCustomerShipmentSales.setShipPostalCode(list.get(shipPostalCodeIndex));
                mwsCustomerShipmentSales.setPointsGranted(ToolUtil.valueOfDouble(list.get(PointsGrantedIndex)));
                mwsCustomerShipmentSales.setCreateTime(DateUtil.getDate());
                mwsCustomerShipmentSales.setUpdateTime(DateUtil.getDate());
                mwsCustomerShipmentSales.setFinanceStatus(1);
                insertList.add(mwsCustomerShipmentSales);
            }
            return insertList;
        } else {
            int shipmentDateIndex = Integer.valueOf(String.valueOf(titleMap.get("shipment-date")));
            int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
            int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
            int asinIndex = Integer.valueOf(String.valueOf(titleMap.get("asin")));
            int fulfillmentCenterIdIndex = Integer.valueOf(String.valueOf(titleMap.get("fulfillment-center-id")));
            int quantityIndex = Integer.valueOf(String.valueOf(titleMap.get("quantity")));
            int amazonOrderIdIndex = Integer.valueOf(String.valueOf(titleMap.get("amazon-order-id")));
            int currencyIndex = Integer.valueOf(String.valueOf(titleMap.get("currency")));
            int itemPricePerUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("item-price-per-unit")));
            int shippingPriceIndex = Integer.valueOf(String.valueOf(titleMap.get("shipping-price")));
            int giftWrapPriceIndex = Integer.valueOf(String.valueOf(titleMap.get("gift-wrap-price")));
            int shipCityIndex = Integer.valueOf(String.valueOf(titleMap.get("ship-city")));
            int shipStateIndex = Integer.valueOf(String.valueOf(titleMap.get("ship-state")));
            int shipPostalCodeIndex = Integer.valueOf(String.valueOf(titleMap.get("ship-postal-code")));
            int pointsGrantedIndex = 9999;
            if (null != titleMap.get("points-granted")) {
                pointsGrantedIndex = Integer.valueOf(String.valueOf(titleMap.get("points-granted")));
            }
//            //获取数据库里面最新的日期
//            String latestDay = mwsCustomerShipmentSalesMapper.getLatestDay(sellerId, area);
            List<Object> insertList = new ArrayList<>(lists.size());
            for (int i = 1; i < lists.size(); i++) {
                List<String> list = lists.get(i);
//                if (!StringUtil.isBlank(latestDay)) {
//                    //如果当前获取的数据的时间和数据库时间一致或者早于数据库时间，本次获取数据将跳过
//                    if (DateUtil.compateTime(latestDay, DateUtil.ISO8601ToGMT(list.get(shipmentDateIndex))) >= 0) {
//                        continue;
//                    }
//                }
                MwsCustomerShipmentSales mwsCustomerShipmentSales = new MwsCustomerShipmentSales();
                mwsCustomerShipmentSales.setSellerId(sellerId);
                mwsCustomerShipmentSales.setArea(area);
                //转为零时区时间
                mwsCustomerShipmentSales.setShipmentDate(DateUtil.ISO8601ToGMT(list.get(shipmentDateIndex)));
                mwsCustomerShipmentSales.setSku(list.get(skuIndex));
                mwsCustomerShipmentSales.setFnsku(list.get(fnskuIndex));
                mwsCustomerShipmentSales.setAsin(list.get(asinIndex));
                mwsCustomerShipmentSales.setFulfillmentCenterId(list.get(fulfillmentCenterIdIndex));
                mwsCustomerShipmentSales.setQuantity(ToolUtil.valueOfInteger(list.get(quantityIndex)));
                mwsCustomerShipmentSales.setAmazonOrderId(list.get(amazonOrderIdIndex));
                mwsCustomerShipmentSales.setCurrency(list.get(currencyIndex));
                mwsCustomerShipmentSales.setItemPricePerUnit(ToolUtil.valueOfDouble(list.get(itemPricePerUnitIndex)));
                mwsCustomerShipmentSales.setShippingPrice(ToolUtil.valueOfDouble(list.get(shippingPriceIndex)));
                mwsCustomerShipmentSales.setGiftWrapPrice(ToolUtil.valueOfDouble(list.get(giftWrapPriceIndex)));
                mwsCustomerShipmentSales.setShipCity(list.get(shipCityIndex));
                mwsCustomerShipmentSales.setShipState(list.get(shipStateIndex));
                mwsCustomerShipmentSales.setShipPostalCode(list.get(shipPostalCodeIndex));
                mwsCustomerShipmentSales.setCreateTime(DateUtil.getDate());
                mwsCustomerShipmentSales.setUpdateTime(DateUtil.getDate());
                if (pointsGrantedIndex != 9999) {
                    mwsCustomerShipmentSales.setPointsGranted(ToolUtil.valueOfDouble(list.get(pointsGrantedIndex)));
                }
                mwsCustomerShipmentSales.setFinanceStatus(1);
                insertList.add(mwsCustomerShipmentSales);
            }
            return insertList;
        }
    }

    /**
     * 移除货件详情 将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleRemovalShipmentDetail(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        int requestDateIndex = Integer.valueOf(String.valueOf(titleMap.get("request-date")));
//        String latestDay = mwsRemovalShipmentDetailMapper.getLatestDay(sellerId, area);
//        if (!StringUtil.isBlank(latestDay)) {
//            //如果当前获取的数据的时间和数据库时间一致或者早于数据库时间，本次获取数据将跳过
//            if (DateUtil.compateTime(latestDay, DateUtil.ISO8601ToGMT(lists.get(1).get(requestDateIndex))) >= 0) {
//                return null;
//            }
//        }
        int orderIdIndex = Integer.valueOf(String.valueOf(titleMap.get("order-id")));
        int shipmentDateIndex = Integer.valueOf(String.valueOf(titleMap.get("shipment-date")));
        int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
        int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
        int dispositionIndex = Integer.valueOf(String.valueOf(titleMap.get("disposition")));
        int shippedQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("shipped-quantity")));
        int carrierIndex = Integer.valueOf(String.valueOf(titleMap.get("carrier")));
        int trackingNumberIndex = Integer.valueOf(String.valueOf(titleMap.get("tracking-number")));
        List<Object> insertList = new ArrayList<>(lists.size());
        for (int i = 1; i < lists.size(); i++) {
            MwsRemovalShipmentDetail mwsRemovalShipmentDetail = new MwsRemovalShipmentDetail();
            List<String> row = lists.get(i);
            mwsRemovalShipmentDetail.setSellerId(sellerId);
            mwsRemovalShipmentDetail.setArea(area);
            mwsRemovalShipmentDetail.setRequestDate(DateUtil.ISO8601ToGMT(row.get(requestDateIndex)));
            mwsRemovalShipmentDetail.setOrderId(row.get(orderIdIndex));
            mwsRemovalShipmentDetail.setShipmentDate(DateUtil.ISO8601ToGMT(row.get(shipmentDateIndex)));
            mwsRemovalShipmentDetail.setSku(row.get(skuIndex));
            mwsRemovalShipmentDetail.setFnsku(row.get(fnskuIndex));
            mwsRemovalShipmentDetail.setDisposition(row.get(dispositionIndex));
            mwsRemovalShipmentDetail.setShippedQuantity(ToolUtil.valueOfInteger(row.get(shippedQuantityIndex)));
            mwsRemovalShipmentDetail.setCarrier(row.get(carrierIndex));
            mwsRemovalShipmentDetail.setTrackingNumber(row.get(trackingNumberIndex));
            MwsRemovalShipmentDetail mwsRemovalShipmentDetailByParam = mwsRemovalShipmentDetailMapper.selectByMwsRemovalShipmentDetailAttribute(mwsRemovalShipmentDetail);
            if (null != mwsRemovalShipmentDetailByParam) {
                continue;
            }
            mwsRemovalShipmentDetail.setCreateTime(DateUtil.getDate());
            mwsRemovalShipmentDetail.setUpdateTime(DateUtil.getDate());
            mwsRemovalShipmentDetail.setFinanceStatus(1);
            insertList.add(mwsRemovalShipmentDetail);
        }
        return insertList;
    }

    /**
     * 移除订单详情 将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @throws Exception
     */
    public void handleRemovalOrderDetail(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        int requestDateIndex = Integer.valueOf(String.valueOf(titleMap.get("request-date")));
        int orderIdIndex = Integer.valueOf(String.valueOf(titleMap.get("order-id")));
        int orderTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("order-type")));
        int orderStatusIndex = Integer.valueOf(String.valueOf(titleMap.get("order-status")));
        int lastUpdatedDateIndex = Integer.valueOf(String.valueOf(titleMap.get("last-updated-date")));
        int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
        int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
        int dispositionIndex = Integer.valueOf(String.valueOf(titleMap.get("disposition")));
        int requestedQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("requested-quantity")));
        int cancelledQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("cancelled-quantity")));
        int disposedQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("disposed-quantity")));
        int shippedQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("shipped-quantity")));
        int inProcessQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("in-process-quantity")));
        int removalFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("removal-fee")));
        int currencyIndex = Integer.valueOf(String.valueOf(titleMap.get("currency")));
        int serviceSpeedIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("service-speed")) {
            serviceSpeedIndex = Integer.valueOf(String.valueOf(titleMap.get("service-speed")));
        }
        List<Object> insertList = new ArrayList<>();
        for (int i = 1; i < lists.size(); i++) {
            MwsRemovalOrderDetail mwsRemovalOrderDetail = new MwsRemovalOrderDetail();
            List<String> row = lists.get(i);
            mwsRemovalOrderDetail.setOrderId(row.get(orderIdIndex));
            mwsRemovalOrderDetail.setSku(row.get(skuIndex));
            mwsRemovalOrderDetail.setFnsku(row.get(fnskuIndex));
            mwsRemovalOrderDetail.setDisposition(row.get(dispositionIndex));
            mwsRemovalOrderDetail.setLastUpdatedDate(DateUtil.ISO8601ToGMT(row.get(lastUpdatedDateIndex)));
            MwsRemovalOrderDetail oneInfoByOrderIdAndSkuAndFnSkuAndDisposition = mwsRemovalOrderDetailMapper.getOneInfoByOrderIdAndSkuAndFnSkuAndDisposition(mwsRemovalOrderDetail.getOrderId(), mwsRemovalOrderDetail.getSku(),
                    mwsRemovalOrderDetail.getFnsku(), mwsRemovalOrderDetail.getDisposition());
            //如果存在该条数据
            if (null != oneInfoByOrderIdAndSkuAndFnSkuAndDisposition && null != oneInfoByOrderIdAndSkuAndFnSkuAndDisposition.getId()) {
                //如果最后更新时间和本次获取的最后更新时间一致
                if (oneInfoByOrderIdAndSkuAndFnSkuAndDisposition.getLastUpdatedDate().equals(mwsRemovalOrderDetail.getLastUpdatedDate())) {
                    continue;
                }
            }
            mwsRemovalOrderDetail.setSellerId(sellerId);
            mwsRemovalOrderDetail.setArea(area);
            mwsRemovalOrderDetail.setRequestDate(DateUtil.ISO8601ToGMT(row.get(requestDateIndex)));
            mwsRemovalOrderDetail.setOrderType(row.get(orderTypeIndex));
            mwsRemovalOrderDetail.setOrderStatus(row.get(orderStatusIndex));
            mwsRemovalOrderDetail.setRequestedQuantity(ToolUtil.valueOfInteger(row.get(requestedQuantityIndex)));
            mwsRemovalOrderDetail.setCancelledQuantity(ToolUtil.valueOfInteger(row.get(cancelledQuantityIndex)));
            mwsRemovalOrderDetail.setDisposedQuantity(ToolUtil.valueOfInteger(row.get(disposedQuantityIndex)));
            mwsRemovalOrderDetail.setShippedQuantity(ToolUtil.valueOfInteger(row.get(shippedQuantityIndex)));
            mwsRemovalOrderDetail.setInProcessQuantity(ToolUtil.valueOfInteger(row.get(inProcessQuantityIndex)));
            mwsRemovalOrderDetail.setRemovalFee(ToolUtil.valueOfDouble(row.get(removalFeeIndex)));
            mwsRemovalOrderDetail.setCurrency(row.get(currencyIndex));
            mwsRemovalOrderDetail.setCreateTime(DateUtil.getDate());
            mwsRemovalOrderDetail.setUpdateTime(DateUtil.getDate());
            if (serviceSpeedIndex != WRONG_NUMBER_SIGN) {
                mwsRemovalOrderDetail.setServiceSpeed(row.get(serviceSpeedIndex));
            }
            if (null == oneInfoByOrderIdAndSkuAndFnSkuAndDisposition || null == oneInfoByOrderIdAndSkuAndFnSkuAndDisposition.getId()) {
                mwsRemovalOrderDetail.setId(null);
                mwsRemovalOrderDetail.setFinanceStatus(1);
                insertList.add(mwsRemovalOrderDetail);
            } else {
                mwsRemovalOrderDetail.setId(oneInfoByOrderIdAndSkuAndFnSkuAndDisposition.getId());
                baseUpdate(mwsRemovalOrderDetail);
            }
        }
        baseInsertList(insertList);
    }

    /**
     * 退货 将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleFbaCustomerReturns(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
//        String latestDay = mwsFbaCustomerReturnsMapper.getLatestDay(sellerId, area);
        int returnDateIndex = Integer.valueOf(String.valueOf(titleMap.get("return-date")));
//        if (!StringUtil.isBlank(latestDay)) {
//            //如果当前获取的数据的时间和数据库时间一致或者早于数据库时间，本次获取数据将跳过
//            if (DateUtil.compateTime(latestDay, DateUtil.ISO8601ToGMT(lists.get(1).get(returnDateIndex))) >= 0) {
//                return null;
//            }
//        }
        int orderIdIndex = Integer.valueOf(String.valueOf(titleMap.get("order-id")));
        int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
        int asinIndex = Integer.valueOf(String.valueOf(titleMap.get("asin")));
        int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
        int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("product-name")));
        int quantityIndex = Integer.valueOf(String.valueOf(titleMap.get("quantity")));
        int fulfillmentCenterIdIndex = Integer.valueOf(String.valueOf(titleMap.get("fulfillment-center-id")));
        int detailedDispositionIndex = Integer.valueOf(String.valueOf(titleMap.get("detailed-disposition")));
        int reasonIndex = Integer.valueOf(String.valueOf(titleMap.get("reason")));
        int statusIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("status")) {
            statusIndex = Integer.valueOf(String.valueOf(titleMap.get("status")));
        }
        int licensePlateNumberIndex = Integer.valueOf(String.valueOf(titleMap.get("license-plate-number")));
        int customerCommentsIndex = Integer.valueOf(String.valueOf(titleMap.get("customer-comments")));
        List<Object> list = new ArrayList<>(lists.size());
        for (int i = 1; i < lists.size(); i++) {
            MwsFbaCustomerReturns mwsFbaCustomerReturns = new MwsFbaCustomerReturns();
            List<String> stringList = lists.get(i);
            mwsFbaCustomerReturns.setSellerId(sellerId);
            mwsFbaCustomerReturns.setArea(area);
            mwsFbaCustomerReturns.setReturnDate(DateUtil.ISO8601ToGMT(stringList.get(returnDateIndex)));
            mwsFbaCustomerReturns.setOrderId(stringList.get(orderIdIndex));
            mwsFbaCustomerReturns.setSku(stringList.get(skuIndex));
            mwsFbaCustomerReturns.setAsin(stringList.get(asinIndex));
            mwsFbaCustomerReturns.setFnsku(stringList.get(fnskuIndex));
            mwsFbaCustomerReturns.setProductName(stringList.get(productNameIndex));
            mwsFbaCustomerReturns.setQuantity(ToolUtil.valueOfInteger(stringList.get(quantityIndex)));
            mwsFbaCustomerReturns.setFulfillmentCenterId(stringList.get(fulfillmentCenterIdIndex));
            mwsFbaCustomerReturns.setDetailedDisposition(stringList.get(detailedDispositionIndex));
            mwsFbaCustomerReturns.setReason(stringList.get(reasonIndex));
            if (statusIndex != WRONG_NUMBER_SIGN) {
                mwsFbaCustomerReturns.setStatus(stringList.get(statusIndex));
            }
            mwsFbaCustomerReturns.setLicensePlateNumber(stringList.get(licensePlateNumberIndex));
            mwsFbaCustomerReturns.setCustomerComments(stringList.get(customerCommentsIndex));
            mwsFbaCustomerReturns.setCreateTime(DateUtil.getDate());
            mwsFbaCustomerReturns.setUpdateTime(DateUtil.getDate());
            mwsFbaCustomerReturns.setFinanceStatus(1);
            list.add(mwsFbaCustomerReturns);
        }
        return list;
    }

    /**
     * 长期仓储费 将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleLongTermStorageFeeCharges(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<Object> insertList = new ArrayList<>(lists.size());
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        if (null != area && area.equals(ESPECIALLY_AREA) && !shopName.equals(SPECIAL_SHOP_NAME)) {
//            //获取数据库里面最新的日期
//            String latestDay = mwsLongTermStorageFeeChargesMapper.getLatestDay(sellerId, area);
            int snapshotDateIndex = Integer.valueOf(String.valueOf(titleMap.get("Date")));
//            if (!StringUtil.isBlank(latestDay)) {
//                //如果当前获取的数据的时间和数据库时间一致或者早于数据库时间，本次获取数据将跳过
//                if (DateUtil.compateTime(latestDay, DateUtil.ISO8601ToGMT(lists.get(1).get(snapshotDateIndex))) >= 0) {
//                    return null;
//                }
//            }
            int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("Merchant SKU")));
            int fnSkuIndex = Integer.valueOf(String.valueOf(titleMap.get("FNSKU")));
            int asinIndex = Integer.valueOf(String.valueOf(titleMap.get("ASIN")));
            int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("Title")));
            int conditionIndex = Integer.valueOf(String.valueOf(titleMap.get("Condition")));
            int qtyCharged12MoLongTermStorageFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("qty-charged-12-mo-long-term-storage-fee")));
            int perUnitVolumeIndex = Integer.valueOf(String.valueOf(titleMap.get("Unit Volume")));
            int currencyIndex = Integer.valueOf(String.valueOf(titleMap.get("Currency")));
            int w12MoLongTermsStorageFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("12-mo-long-term-storage-fee")));
            int qtyCharged6MoLongTermStorageFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("qty-charged-6-mo-long-term-storage-fee")));
            int w6MoLongTermsStorageFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("6-mo-long-term-storage-fee")));
            int volumeUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("volume-unit")));
            int countryIndex = Integer.valueOf(String.valueOf(titleMap.get("Country")));
            int enrolledInSmallAndLightIndex = Integer.valueOf(String.valueOf(titleMap.get("enrolled-in-small-and-light")));

            for (int i = 1; i < lists.size(); i++) {
                MwsLongTermStorageFeeCharges mwsLongTermStorageFeeCharges = new MwsLongTermStorageFeeCharges();
                List<String> row = lists.get(i);
                mwsLongTermStorageFeeCharges.setSellerId(sellerId);
                mwsLongTermStorageFeeCharges.setArea(area);
                mwsLongTermStorageFeeCharges.setSnapshotDate(DateUtil.ISO8601ToGMT(row.get(snapshotDateIndex)));
                mwsLongTermStorageFeeCharges.setSku(row.get(skuIndex));
                mwsLongTermStorageFeeCharges.setFnsku(row.get(fnSkuIndex));
                mwsLongTermStorageFeeCharges.setAsin(row.get(asinIndex));
                mwsLongTermStorageFeeCharges.setProductName(row.get(productNameIndex));
                mwsLongTermStorageFeeCharges.setCondition(row.get(conditionIndex));
                mwsLongTermStorageFeeCharges.setQtyCharged12MoLongTermStorageFee(ToolUtil.valueOfInteger(row.get(qtyCharged12MoLongTermStorageFeeIndex)));
                mwsLongTermStorageFeeCharges.setPerUnitVolume(ToolUtil.valueOfDouble(row.get(perUnitVolumeIndex)));
                mwsLongTermStorageFeeCharges.setCurrency(row.get(currencyIndex));
                mwsLongTermStorageFeeCharges.setMoLongTermsStorageFee12(ToolUtil.valueOfBigDecimal(row.get(w12MoLongTermsStorageFeeIndex)));
                mwsLongTermStorageFeeCharges.setQtyCharged6MoLongTermStorageFee(ToolUtil.valueOfDouble(row.get(qtyCharged6MoLongTermStorageFeeIndex)));
                mwsLongTermStorageFeeCharges.setMoLongTermsStorageFee6(ToolUtil.valueOfBigDecimal(row.get(w6MoLongTermsStorageFeeIndex)));
                mwsLongTermStorageFeeCharges.setVolumeUnit(row.get(volumeUnitIndex));
                mwsLongTermStorageFeeCharges.setCountry(row.get(countryIndex));
                mwsLongTermStorageFeeCharges.setEnrolledInSmallAndLight(row.get(enrolledInSmallAndLightIndex));
                mwsLongTermStorageFeeCharges.setCreateTime(DateUtil.getDate());
                mwsLongTermStorageFeeCharges.setUpdateTime(DateUtil.getDate());
                mwsLongTermStorageFeeCharges.setFinanceStatus(1);
                insertList.add(mwsLongTermStorageFeeCharges);
            }
            return insertList;
        } else {
            //获取数据库里面最新的日期
//            String latestDay = mwsLongTermStorageFeeChargesMapper.getLatestDay(sellerId, area);
            int snapshotDateIndex = Integer.valueOf(String.valueOf(titleMap.get("snapshot-date")));
//            if (!StringUtil.isBlank(latestDay)) {
//                //如果当前获取的数据的时间和数据库时间一致或者早于数据库时间，本次获取数据将跳过
//                if (DateUtil.compateTime(latestDay, DateUtil.ISO8601ToGMT(lists.get(1).get(snapshotDateIndex))) >= 0) {
//                    return null;
//                }
//            }
            int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
            int fnSkuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
            int asinIndex = Integer.valueOf(String.valueOf(titleMap.get("asin")));
            int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("product-name")));
            int conditionIndex = Integer.valueOf(String.valueOf(titleMap.get("condition")));
            int qtyCharged12MoLongTermStorageFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("qty-charged-12-mo-long-term-storage-fee")));
            int perUnitVolumeIndex = Integer.valueOf(String.valueOf(titleMap.get("per-unit-volume")));
            int currencyIndex = Integer.valueOf(String.valueOf(titleMap.get("currency")));
            int w12MoLongTermsStorageFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("12-mo-long-terms-storage-fee")));
            int qtyCharged6MoLongTermStorageFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("qty-charged-6-mo-long-term-storage-fee")));
            int w6MoLongTermsStorageFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("6-mo-long-terms-storage-fee")));
            int volumeUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("volume-unit")));
            int countryIndex = Integer.valueOf(String.valueOf(titleMap.get("country")));
            int enrolledInSmallAndLightIndex = Integer.valueOf(String.valueOf(titleMap.get("enrolled-in-small-and-light")));
            for (int i = 1; i < lists.size(); i++) {
                MwsLongTermStorageFeeCharges mwsLongTermStorageFeeCharges = new MwsLongTermStorageFeeCharges();
                List<String> row = lists.get(i);
                mwsLongTermStorageFeeCharges.setSellerId(sellerId);
                mwsLongTermStorageFeeCharges.setArea(area);
                mwsLongTermStorageFeeCharges.setSnapshotDate(DateUtil.ISO8601ToGMT(row.get(snapshotDateIndex)));
                mwsLongTermStorageFeeCharges.setSku(row.get(skuIndex));
                mwsLongTermStorageFeeCharges.setFnsku(row.get(fnSkuIndex));
                mwsLongTermStorageFeeCharges.setAsin(row.get(asinIndex));
                mwsLongTermStorageFeeCharges.setProductName(row.get(productNameIndex));
                mwsLongTermStorageFeeCharges.setCondition(row.get(conditionIndex));
                mwsLongTermStorageFeeCharges.setQtyCharged12MoLongTermStorageFee(ToolUtil.valueOfInteger(row.get(qtyCharged12MoLongTermStorageFeeIndex)));
                mwsLongTermStorageFeeCharges.setPerUnitVolume(ToolUtil.valueOfDouble(row.get(perUnitVolumeIndex)));
                mwsLongTermStorageFeeCharges.setCurrency(row.get(currencyIndex));
                mwsLongTermStorageFeeCharges.setMoLongTermsStorageFee12(ToolUtil.valueOfBigDecimal(row.get(w12MoLongTermsStorageFeeIndex)));
                mwsLongTermStorageFeeCharges.setQtyCharged6MoLongTermStorageFee(ToolUtil.valueOfDouble(row.get(qtyCharged6MoLongTermStorageFeeIndex)));
                mwsLongTermStorageFeeCharges.setMoLongTermsStorageFee6(ToolUtil.valueOfBigDecimal(row.get(w6MoLongTermsStorageFeeIndex)));
                mwsLongTermStorageFeeCharges.setVolumeUnit(row.get(volumeUnitIndex));
                mwsLongTermStorageFeeCharges.setCountry(row.get(countryIndex));
                mwsLongTermStorageFeeCharges.setEnrolledInSmallAndLight(row.get(enrolledInSmallAndLightIndex));
                mwsLongTermStorageFeeCharges.setCreateTime(DateUtil.getDate());
                mwsLongTermStorageFeeCharges.setUpdateTime(DateUtil.getDate());
                mwsLongTermStorageFeeCharges.setFinanceStatus(1);
                insertList.add(mwsLongTermStorageFeeCharges);
            }
            return insertList;
        }
    }


    /**
     * 费用预览 将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @throws Exception
     */
    public List<Object> handleFeePreview(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<Object> insertList = new ArrayList<>(lists.size());
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
        int fnSkuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
        int asinIndex = Integer.valueOf(String.valueOf(titleMap.get("asin")));
        int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("product-name")));
        int productGroupIndex = Integer.valueOf(String.valueOf(titleMap.get("product-group")));
        int brandIndex = Integer.valueOf(String.valueOf(titleMap.get("brand")));
        int fulfilledByIndex = Integer.valueOf(String.valueOf(titleMap.get("fulfilled-by")));
        int yourPriceIndex = Integer.valueOf(String.valueOf(titleMap.get("your-price")));
        int salesPriceIndex = Integer.valueOf(String.valueOf(titleMap.get("sales-price")));
        int longestSideIndex = Integer.valueOf(String.valueOf(titleMap.get("longest-side")));
        int medianSideIndex = Integer.valueOf(String.valueOf(titleMap.get("median-side")));
        int shortestSideIndex = Integer.valueOf(String.valueOf(titleMap.get("shortest-side")));
        int lengthAndGirthIndex = Integer.valueOf(String.valueOf(titleMap.get("length-and-girth")));
        int unitOfDimensionIndex = Integer.valueOf(String.valueOf(titleMap.get("unit-of-dimension")));
        int itemPackageWeightIndex = Integer.valueOf(String.valueOf(titleMap.get("item-package-weight")));
        int unitOfWeightIndex = Integer.valueOf(String.valueOf(titleMap.get("unit-of-weight")));
        int currencyIndex = Integer.valueOf(String.valueOf(titleMap.get("currency")));
        int estimatedFeeTotalIndex = Integer.valueOf(String.valueOf(titleMap.get("estimated-fee-total")));
        int estimatedReferralFeePerUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("estimated-referral-fee-per-unit")));
        int estimatedVariableClosingFeeIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("estimated-variable-closing-fee")) {
            estimatedVariableClosingFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("estimated-variable-closing-fee")));
        }
        int estimatedPickPackFeePerUnitIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("estimated-pick-pack-fee-per-unit")) {
            estimatedPickPackFeePerUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("estimated-pick-pack-fee-per-unit")));
        }
        int estimatedWeightHandlingFeePerUnitIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("estimated-weight-handling-fee-per-unit")) {
            estimatedWeightHandlingFeePerUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("estimated-weight-handling-fee-per-unit")));
        }
        int expectedFulfillmentFeePerUnitIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-fulfillment-fee-per-unit")) {
            expectedFulfillmentFeePerUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-fulfillment-fee-per-unit")));
        }
        int hasLocalInventoryIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("has-local-inventory")) {
            hasLocalInventoryIndex = Integer.valueOf(String.valueOf(titleMap.get("has-local-inventory")));
        }
        int productSizeWeightBandIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("product-size-weight-band")) {
            productSizeWeightBandIndex = Integer.valueOf(String.valueOf(titleMap.get("product-size-weight-band")));
        }
        int estimatedOrderHandlingFeePerOrderIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("estimated-order-handling-fee-per-order")) {
            estimatedOrderHandlingFeePerOrderIndex = Integer.valueOf(String.valueOf(titleMap.get("estimated-order-handling-fee-per-order")));
        }
        int expectedDomesticFulfilmentFeePerUnitIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-domestic-fulfilment-fee-per-unit")) {
            expectedDomesticFulfilmentFeePerUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-domestic-fulfilment-fee-per-unit")));
        }
        int expectedEfnFulfilmentFeePerUnitUkIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-efn-fulfilment-fee-per-unit-uk")) {
            expectedEfnFulfilmentFeePerUnitUkIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-efn-fulfilment-fee-per-unit-uk")));
        }
        int expectedEfnFulfilmentFeePerUnitDeIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-efn-fulfilment-fee-per-unit-de")) {
            expectedEfnFulfilmentFeePerUnitDeIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-efn-fulfilment-fee-per-unit-de")));
        }
        int expectedEfnFulfilmentFeePerUnitFrIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-efn-fulfilment-fee-per-unit-fr")) {
            expectedEfnFulfilmentFeePerUnitFrIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-efn-fulfilment-fee-per-unit-fr")));
        }
        int expectedEfnFulfilmentFeePerUnitItIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-efn-fulfilment-fee-per-unit-it")) {
            expectedEfnFulfilmentFeePerUnitItIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-efn-fulfilment-fee-per-unit-it")));
        }
        int expectedEfnFulfilmentFeePerUnitEsIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-efn-fulfilment-fee-per-unit-es")) {
            expectedEfnFulfilmentFeePerUnitEsIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-efn-fulfilment-fee-per-unit-es")));
        }
        int expectedFutureFulfilmentFeePrtUnitUkIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-future-fulfilment-fee-prt-unit-uk")) {
            expectedFutureFulfilmentFeePrtUnitUkIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-future-fulfilment-fee-prt-unit-uk")));
        }
        int expectedFutureFulfilmentFeePrtUnitDeIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-future-fulfilment-fee-prt-unit-de")) {
            expectedFutureFulfilmentFeePrtUnitDeIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-future-fulfilment-fee-prt-unit-de")));
        }
        int expectedFutureFulfilmentFeePrtUnitFrIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-future-fulfilment-fee-prt-unit-fr")) {
            expectedFutureFulfilmentFeePrtUnitFrIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-future-fulfilment-fee-prt-unit-fr")));
        }
        int expectedFutureFulfilmentFeePrtUnitItIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-future-fulfilment-fee-prt-unit-it")) {
            expectedFutureFulfilmentFeePrtUnitItIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-future-fulfilment-fee-prt-unit-it")));
        }
        int expectedFutureFulfilmentFeePrtUnitEsIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-future-fulfilment-fee-prt-unit-es")) {
            expectedFutureFulfilmentFeePrtUnitEsIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-future-fulfilment-fee-prt-unit-es")));
        }
        int estimatedFutureDomesticFulfilmentFeePerUnitIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("estimated-future-domestic-fulfilment-fee-per-unit")) {
            estimatedFutureDomesticFulfilmentFeePerUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("estimated-future-domestic-fulfilment-fee-per-unit")));
        }
        int estimatedFutureOrderHandlingFeePerOrderIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("estimated-future-order-handling-fee-per-order")) {
            estimatedFutureOrderHandlingFeePerOrderIndex = Integer.valueOf(String.valueOf(titleMap.get("estimated-future-order-handling-fee-per-order")));
        }
        int expectedFutureDomesticFulfilmentFeePerUnitIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("expected-future-domestic-fulfilment-fee-per-unit")) {
            expectedFutureDomesticFulfilmentFeePerUnitIndex = Integer.valueOf(String.valueOf(titleMap.get("expected-future-domestic-fulfilment-fee-per-unit")));
        }
        int estimatedFutureFeeIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("estimated-future-fee")) {
            estimatedFutureFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("estimated-future-fee")));
        }
        for (int i = 1; i < lists.size(); i++) {
            MwsFeePreview mwsFeePreview = new MwsFeePreview();
            List<String> row = lists.get(i);
            mwsFeePreview.setSellerId(sellerId);
            mwsFeePreview.setArea(area);
            mwsFeePreview.setSku(row.get(skuIndex));
            mwsFeePreview.setFnsku(row.get(fnSkuIndex));
            mwsFeePreview.setAsin(row.get(asinIndex));
            mwsFeePreview.setProductName(row.get(productNameIndex));
            mwsFeePreview.setProductGroup(row.get(productGroupIndex));
            mwsFeePreview.setBrand(row.get(brandIndex));
            mwsFeePreview.setFulfilledBy(row.get(fulfilledByIndex));
            mwsFeePreview.setYourPrice(row.get(yourPriceIndex));
            mwsFeePreview.setSalesPrice(row.get(salesPriceIndex));
            mwsFeePreview.setLongestSide(row.get(longestSideIndex));
            mwsFeePreview.setMedianSide(row.get(medianSideIndex));
            mwsFeePreview.setShortestSide(row.get(shortestSideIndex));
            mwsFeePreview.setLengthAndGirth(row.get(lengthAndGirthIndex));
            mwsFeePreview.setUnitOfDimension(row.get(unitOfDimensionIndex));
            mwsFeePreview.setItemPackageWeight(row.get(itemPackageWeightIndex));
            mwsFeePreview.setUnitOfWeight(row.get(unitOfWeightIndex));
            mwsFeePreview.setCurrency(row.get(currencyIndex));
            mwsFeePreview.setEstimatedFeeTotal(row.get(estimatedFeeTotalIndex));
            mwsFeePreview.setEstimatedReferralFeePerUnit(row.get(estimatedReferralFeePerUnitIndex));
            mwsFeePreview.setCreateTime(DateUtil.getDate());
            mwsFeePreview.setUpdateTime(DateUtil.getDate());
            if (estimatedVariableClosingFeeIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setEstimatedVariableClosingFee(row.get(estimatedVariableClosingFeeIndex));
            }
            if (estimatedPickPackFeePerUnitIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setEstimatedPickPackFeePerUnit(row.get(estimatedPickPackFeePerUnitIndex));
            }
            if (estimatedWeightHandlingFeePerUnitIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setEstimatedWeightHandlingFeePerUnit(row.get(estimatedWeightHandlingFeePerUnitIndex));
            }
            if (expectedFulfillmentFeePerUnitIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedFulfillmentFeePerUnit(row.get(expectedFulfillmentFeePerUnitIndex));
            }
            mwsFeePreview.setCreateTime(DateUtil.getDate());
            if (hasLocalInventoryIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setHasLocalInventory(row.get(hasLocalInventoryIndex));
            }
            if (productSizeWeightBandIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setProductSizeWeightBand(row.get(productSizeWeightBandIndex));
            }
            if (estimatedOrderHandlingFeePerOrderIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setEstimatedOrderHandlingFeePerOrder(row.get(estimatedOrderHandlingFeePerOrderIndex));
            }
            if (expectedDomesticFulfilmentFeePerUnitIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedDomesticFulfilmentFeePerUnit(row.get(expectedDomesticFulfilmentFeePerUnitIndex));
            }
            if (expectedEfnFulfilmentFeePerUnitUkIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedEfnFulfilmentFeePerUnitUk(row.get(expectedEfnFulfilmentFeePerUnitUkIndex));
            }
            if (expectedEfnFulfilmentFeePerUnitDeIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedEfnFulfilmentFeePerUnitDe(row.get(expectedEfnFulfilmentFeePerUnitDeIndex));
            }
            if (expectedEfnFulfilmentFeePerUnitFrIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedEfnFulfilmentFeePerUnitFr(row.get(expectedEfnFulfilmentFeePerUnitFrIndex));
            }
            if (expectedEfnFulfilmentFeePerUnitItIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedEfnFulfilmentFeePerUnitIt(row.get(expectedEfnFulfilmentFeePerUnitItIndex));
            }
            if (expectedEfnFulfilmentFeePerUnitEsIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedEfnFulfilmentFeePerUnitEs(row.get(expectedEfnFulfilmentFeePerUnitEsIndex));
            }
            if (expectedFutureFulfilmentFeePrtUnitUkIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedFutureFulfilmentFeePrtUnitUk(row.get(expectedFutureFulfilmentFeePrtUnitUkIndex));
            }
            if (expectedFutureFulfilmentFeePrtUnitDeIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedFutureFulfilmentFeePrtUnitDe(row.get(expectedFutureFulfilmentFeePrtUnitDeIndex));
            }
            if (expectedFutureFulfilmentFeePrtUnitFrIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedFutureFulfilmentFeePrtUnitFr(row.get(expectedFutureFulfilmentFeePrtUnitFrIndex));
            }
            if (expectedFutureFulfilmentFeePrtUnitItIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedFutureFulfilmentFeePrtUnitIt(row.get(expectedFutureFulfilmentFeePrtUnitItIndex));
            }
            if (expectedFutureFulfilmentFeePrtUnitEsIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedFutureFulfilmentFeePrtUnitEs(row.get(expectedFutureFulfilmentFeePrtUnitEsIndex));
            }
            if (estimatedFutureDomesticFulfilmentFeePerUnitIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setEstimatedFutureDomesticFulfilmentFeePerUnit(row.get(estimatedFutureDomesticFulfilmentFeePerUnitIndex));
            }
            if (estimatedFutureOrderHandlingFeePerOrderIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setEstimatedFutureOrderHandlingFeePerOrder(row.get(estimatedFutureOrderHandlingFeePerOrderIndex));
            }
            if (expectedFutureDomesticFulfilmentFeePerUnitIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setExpectedFutureDomesticFulfilmentFeePerUnit(row.get(expectedFutureDomesticFulfilmentFeePerUnitIndex));
            }
            if (estimatedFutureFeeIndex != WRONG_NUMBER_SIGN) {
                mwsFeePreview.setEstimatedFutureFee(row.get(estimatedFutureFeeIndex));
            }
            mwsFeePreview.setFinanceStatus(1);
            insertList.add(mwsFeePreview);
        }
        return insertList;
    }


    /**
     * 月储存费用   将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleMonthlyStorageFees(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        int monthOfChargeIndex = Integer.valueOf(String.valueOf(titleMap.get("month-of-charge")));
        //获取数据库里面最新的日期
//        String latestDay = mwsMonthlyStorageFeesMapper.getLatestDay(sellerId, area);
//        if (!StringUtil.isBlank(latestDay)) {
//            List<String> firstList = lists.get(1);
//            //如果当前获取的数据的时间和数据库时间一致或者早于数据库时间，本次获取数据将跳过
//            if (DateUtil.compateTime(latestDay, firstList.get(monthOfChargeIndex)) >= 0) {
//                return null;
//            }
//        }
        int asinIndex = Integer.valueOf(String.valueOf(titleMap.get("asin")));
        int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
        int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("product-name")));
        int fulfillmentCenterIndex = Integer.valueOf(String.valueOf(titleMap.get("fulfillment-center")));
        int countryCodeIndex = Integer.valueOf(String.valueOf(titleMap.get("country-code")));
        int longestSideIndex = Integer.valueOf(String.valueOf(titleMap.get("longest-side")));
        int medianSideIndex = Integer.valueOf(String.valueOf(titleMap.get("median-side")));
        int shortestSideIndex = Integer.valueOf(String.valueOf(titleMap.get("shortest-side")));
        int measurementUnitsIndex = Integer.valueOf(String.valueOf(titleMap.get("measurement-units")));
        int weightIndex = Integer.valueOf(String.valueOf(titleMap.get("weight")));
        int weightUnitsIndex = Integer.valueOf(String.valueOf(titleMap.get("weight-units")));
        int itemVolumeIndex = Integer.valueOf(String.valueOf(titleMap.get("item-volume")));
        int volumeUnitsIndex = Integer.valueOf(String.valueOf(titleMap.get("volume-units")));
        int averageQuantityOnHandIndex = Integer.valueOf(String.valueOf(titleMap.get("average-quantity-on-hand")));
        int averageQuantityPendingRemovalIndex = Integer.valueOf(String.valueOf(titleMap.get("average-quantity-pending-removal")));
        int estimatedTotalItemVolumeIndex = Integer.valueOf(String.valueOf(titleMap.get("estimated-total-item-volume")));
        int storageRateIndex = Integer.valueOf(String.valueOf(titleMap.get("storage-rate")));
        int currencyIndex = Integer.valueOf(String.valueOf(titleMap.get("currency")));
        int estimatedMonthlyStorageFeeIndex = Integer.valueOf(String.valueOf(titleMap.get("estimated-monthly-storage-fee")));
        int productSizeTierIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("product-size-tier")) {
            productSizeTierIndex = Integer.valueOf(String.valueOf(titleMap.get("product-size-tier")));
        }
        int dangerousGoodsStorageTypeIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("dangerous-goods-storage-type")) {
            dangerousGoodsStorageTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("dangerous-goods-storage-type")));
        }
        int categoryIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("category")) {
            categoryIndex = Integer.valueOf(String.valueOf(titleMap.get("category")));
        }
        int eligibleForInventoryDiscountIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("eligible-for-inventory-discount")) {
            eligibleForInventoryDiscountIndex = Integer.valueOf(String.valueOf(titleMap.get("eligible-for-inventory-discount")));
        }
        int qualifiesForInventoryDiscountIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("qualifies-for-inventory-discount")) {
            qualifiesForInventoryDiscountIndex = Integer.valueOf(String.valueOf(titleMap.get("qualifies-for-inventory-discount")));
        }
        List<Object> insertList = new ArrayList<>(lists.size());
        for (int i = 1; i < lists.size(); i++) {
            MwsMonthlyStorageFees mwsMonthlyStorageFees = new MwsMonthlyStorageFees();
            List<String> row = lists.get(i);
            mwsMonthlyStorageFees.setSellerId(sellerId);
            mwsMonthlyStorageFees.setArea(area);
            mwsMonthlyStorageFees.setAsin(row.get(asinIndex));
            mwsMonthlyStorageFees.setFnsku(row.get(fnskuIndex));
            mwsMonthlyStorageFees.setProductName(row.get(productNameIndex));
            mwsMonthlyStorageFees.setFulfillmentCenter(row.get(fulfillmentCenterIndex));
            mwsMonthlyStorageFees.setCountryCode(row.get(countryCodeIndex));
            mwsMonthlyStorageFees.setLongestSide(ToolUtil.valueOfDouble(row.get(longestSideIndex)));
            mwsMonthlyStorageFees.setMedianSide(ToolUtil.valueOfDouble(row.get(medianSideIndex)));
            mwsMonthlyStorageFees.setShortestSide(ToolUtil.valueOfDouble(row.get(shortestSideIndex)));
            mwsMonthlyStorageFees.setMeasurementUnits(row.get(measurementUnitsIndex));
            mwsMonthlyStorageFees.setWeight(ToolUtil.valueOfDouble(row.get(weightIndex)));
            mwsMonthlyStorageFees.setWeightUnits(row.get(weightUnitsIndex));
            mwsMonthlyStorageFees.setItemVolume(ToolUtil.valueOfDouble(row.get(itemVolumeIndex)));
            mwsMonthlyStorageFees.setVolumeUnits(row.get(volumeUnitsIndex));
            mwsMonthlyStorageFees.setAverageQuantityOnHand(ToolUtil.valueOfDouble(row.get(averageQuantityOnHandIndex)));
            mwsMonthlyStorageFees.setAverageQuantityPendingRemoval((row.get(averageQuantityPendingRemovalIndex)));
            mwsMonthlyStorageFees.setEstimatedTotalItemVolume(ToolUtil.valueOfDouble(row.get(estimatedTotalItemVolumeIndex)));
            mwsMonthlyStorageFees.setMonthOfCharge(row.get(monthOfChargeIndex));
            mwsMonthlyStorageFees.setStorageRate(ToolUtil.valueOfDouble(row.get(storageRateIndex)));
            mwsMonthlyStorageFees.setCurrency(row.get(currencyIndex));
            mwsMonthlyStorageFees.setEstimatedMonthlyStorageFee(ToolUtil.valueOfDouble(row.get(estimatedMonthlyStorageFeeIndex)));
            mwsMonthlyStorageFees.setCreateTime(DateUtil.getDate());
            mwsMonthlyStorageFees.setUpdateTime(DateUtil.getDate());
            if (productSizeTierIndex != WRONG_NUMBER_SIGN) {
                mwsMonthlyStorageFees.setProductSizeTier(row.get(productSizeTierIndex));
            }
            if (dangerousGoodsStorageTypeIndex != WRONG_NUMBER_SIGN) {
                mwsMonthlyStorageFees.setDangerousGoodsStorageType(row.get(dangerousGoodsStorageTypeIndex));
            }
            if (categoryIndex != WRONG_NUMBER_SIGN) {
                mwsMonthlyStorageFees.setCategory(row.get(categoryIndex));
            }
            if (eligibleForInventoryDiscountIndex != WRONG_NUMBER_SIGN) {
                mwsMonthlyStorageFees.setEligibleForInventoryDiscount(row.get(eligibleForInventoryDiscountIndex));
            }
            if (qualifiesForInventoryDiscountIndex != WRONG_NUMBER_SIGN) {
                mwsMonthlyStorageFees.setQualifiesForInventoryDiscount(row.get(qualifiesForInventoryDiscountIndex));
            }
            mwsMonthlyStorageFees.setFinanceStatus(1);
            insertList.add(mwsMonthlyStorageFees);
        }
        return insertList;
    }


    /**
     * 每月库存记录  将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleEveryMonthInventoryRecords(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<Object> insertList = new ArrayList<>(lists.size());
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        // 不用区域的表头有所不同，目前是日本区域的不同需要做额外处理
        if (null != area && area.equals(ESPECIALLY_AREA) && !shopName.equals(SPECIAL_SHOP_NAME_TWO) && !shopName.equals(SPECIAL_SHOP_NAME)) {
            int monthIndex = Integer.valueOf(String.valueOf(titleMap.get("Month")));
            String latestDay = mwsMonthlyInventoryHistoryMapper.getLatestDay(sellerId, area);
            if (!StringUtil.isBlank(latestDay)) {
                List<String> firstList = lists.get(1);
                String m = firstList.get(monthIndex);
                if (DateUtil.compateTime(latestDay, (m.substring(3, 7) + "-" + m.substring(0, 2))) >= 0) {
                    return null;
                }
            }
            int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("FNSKU")));
            int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("Merchant SKU")));
            int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("Title")));
            int averageQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("Avg Qty")));
            int endQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("End Qty")));
            int fulfillmentCenterIdIndex = Integer.valueOf(String.valueOf(titleMap.get("FC")));
            int detailedDispositionIndex = Integer.valueOf(String.valueOf(titleMap.get("Disposition")));
            int countryIndex = Integer.valueOf(String.valueOf(titleMap.get("Country")));
            for (int i = 1; i < lists.size(); i++) {
                MwsMonthlyInventoryHistory records = new MwsMonthlyInventoryHistory();
                List<String> row = lists.get(i);
                records.setSellerId(sellerId);
                records.setArea(area);
                String month = row.get(monthIndex);
                records.setMonth(month.substring(3, 7) + "-" + month.substring(0, 2));
                records.setFnsku(row.get(fnskuIndex));
                records.setSku(row.get(skuIndex));
                records.setProductName(row.get(productNameIndex));
                records.setAverageQuantity(ToolUtil.valueOfDouble(row.get(averageQuantityIndex)));
                records.setEndQuantity(ToolUtil.valueOfDouble(row.get(endQuantityIndex)));
                records.setFulfillmentCenterId(row.get(fulfillmentCenterIdIndex));
                records.setDetailedDisposition(row.get(detailedDispositionIndex));
                records.setCountry(row.get(countryIndex));
                records.setCreateTime(DateUtil.getDate());
                records.setUpdateTime(DateUtil.getDate());
                records.setFinanceStatus(1);
                insertList.add(records);
            }
            return insertList;
        } else {
            int monthIndex = Integer.valueOf(String.valueOf(titleMap.get("month")));
            String latestDay = mwsMonthlyInventoryHistoryMapper.getLatestDay(sellerId, area);
            if (!StringUtil.isBlank(latestDay)) {
                List<String> firstList = lists.get(1);
                String m = firstList.get(monthIndex);
                if (DateUtil.compateTime(latestDay, (m.substring(3, 7) + "-" + m.substring(0, 2))) >= 0) {
                    return null;
                }
            }
            int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
            int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
            int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("product-name")));
            int averageQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("average-quantity")));
            int endQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("end-quantity")));
            int fulfillmentCenterIdIndex = Integer.valueOf(String.valueOf(titleMap.get("fulfillment-center-id")));
            int detailedDispositionIndex = Integer.valueOf(String.valueOf(titleMap.get("detailed-disposition")));
            int countryIndex = Integer.valueOf(String.valueOf(titleMap.get("country")));
            for (int i = 1; i < lists.size(); i++) {
                MwsMonthlyInventoryHistory records = new MwsMonthlyInventoryHistory();
                List<String> row = lists.get(i);
                records.setSellerId(sellerId);
                records.setArea(area);
                String month = row.get(monthIndex);
                records.setMonth(month.substring(3, 7) + "-" + month.substring(0, 2));
                records.setFnsku(row.get(fnskuIndex));
                records.setSku(row.get(skuIndex));
                records.setProductName(row.get(productNameIndex));
                records.setAverageQuantity(ToolUtil.valueOfDouble(row.get(averageQuantityIndex)));
                records.setEndQuantity(ToolUtil.valueOfDouble(row.get(endQuantityIndex)));
                records.setFulfillmentCenterId(row.get(fulfillmentCenterIdIndex));
                records.setDetailedDisposition(row.get(detailedDispositionIndex));
                records.setCountry(row.get(countryIndex));
                records.setCreateTime(DateUtil.getDate());
                records.setUpdateTime(DateUtil.getDate());
                records.setFinanceStatus(1);
                insertList.add(records);
            }
            return insertList;
        }
    }

    /**
     * 已接收库存 将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleReceiveInventory(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        int receivedDateIndex = Integer.valueOf(String.valueOf(titleMap.get("received-date")));
        int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
        int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
        int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("product-name")));
        int quantityIndex = Integer.valueOf(String.valueOf(titleMap.get("quantity")));
        int fbaShipmentIdIndex = Integer.valueOf(String.valueOf(titleMap.get("fba-shipment-id")));
        int fulfillmentCenterIdIndex = Integer.valueOf(String.valueOf(titleMap.get("fulfillment-center-id")));
        List<Object> insertList = new ArrayList<>(lists.size());
        for (int i = 1; i < lists.size(); i++) {
            MwsReceiveInventory inventory = new MwsReceiveInventory();
            List<String> row = lists.get(i);
            inventory.setSellerId(sellerId);
            inventory.setArea(area);
            inventory.setReceivedDate(DateUtil.ISO8601ToGMT(row.get(receivedDateIndex)));
            inventory.setFnsku(row.get(fnskuIndex));
            inventory.setSku(row.get(skuIndex));
            inventory.setProductName(row.get(productNameIndex));
            inventory.setQuantity(ToolUtil.valueOfInteger(row.get(quantityIndex)));
            inventory.setFbaShipmentId(row.get(fbaShipmentIdIndex));
            inventory.setFulfillmentCenterId(row.get(fulfillmentCenterIdIndex));
            inventory.setCreateTime(DateUtil.getDate());
            inventory.setUpdateTime(DateUtil.getDate());
            inventory.setFinanceStatus(1);
            insertList.add(inventory);
        }
        return insertList;
    }


    /**
     * 盘库 将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shopName
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleInventory(List<List<String>> lists, String shopName, String area) throws Exception {
        Shop shop = shopMapper.getOneInfoByBrandName(shopName);
        String sellerId = shop.getSellerId();
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        int adjustedDateIndex = Integer.valueOf(String.valueOf(titleMap.get("adjusted-date")));
        String latestDay = mwsInventoryAdjustmentsMapper.getLatestDay(sellerId, area);
        if (!StringUtil.isBlank(latestDay)) {
            List<String> firstList = lists.get(1);
            if (DateUtil.compateTime(latestDay, DateUtil.ISO8601ToGMT(firstList.get(adjustedDateIndex))) >= 0) {
                return null;
            }
        }
        int transactionItemIdIndex = Integer.valueOf(String.valueOf(titleMap.get("transaction-item-id")));
        int fnskuIndex = Integer.valueOf(String.valueOf(titleMap.get("fnsku")));
        int skuIndex = Integer.valueOf(String.valueOf(titleMap.get("sku")));
        int productNameIndex = Integer.valueOf(String.valueOf(titleMap.get("product-name")));
        int fulfillmentCenterIdIndex = Integer.valueOf(String.valueOf(titleMap.get("fulfillment-center-id")));
        int quantityIndex = Integer.valueOf(String.valueOf(titleMap.get("quantity")));
        int reasonIndex = Integer.valueOf(String.valueOf(titleMap.get("reason")));
        int dispositionIndex = Integer.valueOf(String.valueOf(titleMap.get("disposition")));
        int reconciledIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("reconciled")) {
            reconciledIndex = Integer.valueOf(String.valueOf(titleMap.get("reconciled")));
        }
        int unreconciledIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("unreconciled")) {
            unreconciledIndex = Integer.valueOf(String.valueOf(titleMap.get("unreconciled")));
        }

        List<Object> list = new ArrayList<>(lists.size());
        for (int i = 1; i < lists.size(); i++) {
            MwsInventoryAdjustments mwsInventoryAdjustments = new MwsInventoryAdjustments();
            List<String> row = lists.get(i);
            mwsInventoryAdjustments.setSellerId(sellerId);
            mwsInventoryAdjustments.setArea(area);
            mwsInventoryAdjustments.setAdjustedDate(DateUtil.ISO8601ToGMT(row.get(adjustedDateIndex)));
            mwsInventoryAdjustments.setTransactionItemId(row.get(transactionItemIdIndex));
            mwsInventoryAdjustments.setFnsku(row.get(fnskuIndex));
            mwsInventoryAdjustments.setSku(row.get(skuIndex));
            mwsInventoryAdjustments.setProductName(row.get(productNameIndex));
            mwsInventoryAdjustments.setFulfillmentCenterId(row.get(fulfillmentCenterIdIndex));
            mwsInventoryAdjustments.setQuantity(ToolUtil.valueOfInteger(row.get(quantityIndex)));
            mwsInventoryAdjustments.setReason(row.get(reasonIndex));
            mwsInventoryAdjustments.setDisposition(row.get(dispositionIndex));
            mwsInventoryAdjustments.setCreateTime(DateUtil.getDate());
            mwsInventoryAdjustments.setUpdateTime(DateUtil.getDate());
            if (reconciledIndex != WRONG_NUMBER_SIGN) {
                mwsInventoryAdjustments.setReconciled(ToolUtil.valueOfInteger(row.get(reconciledIndex)));
            }
            if (unreconciledIndex != WRONG_NUMBER_SIGN) {
                mwsInventoryAdjustments.setUnreconciled(ToolUtil.valueOfInteger(row.get(unreconciledIndex)));
            }
            mwsInventoryAdjustments.setFinanceStatus(1);
            list.add(mwsInventoryAdjustments);
        }
        return list;
    }

    /**
     * 管理亚马逊库存  将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shop
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleManageFbaInventory(List<List<String>> lists, String shop, String area) throws Exception {
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        int skuIndex = Integer.valueOf(titleMap.get("sku").toString());
        int fnskuIndex = Integer.parseInt(titleMap.get("fnsku").toString());
        int asinIndex = Integer.valueOf(titleMap.get("asin").toString());
        int productNameIndex = Integer.valueOf(titleMap.get("product-name").toString());
        int conditionNameIndex = Integer.valueOf(titleMap.get("condition").toString());
        int yourPriceIndex = Integer.valueOf(titleMap.get("your-price").toString());
        int mfnListingExistsIndex = Integer.valueOf(titleMap.get("mfn-listing-exists").toString());
        int mfnFulfillableQuantityIndex = Integer.valueOf(titleMap.get("mfn-fulfillable-quantity").toString());
        int afnListingExistsIndex = Integer.valueOf(titleMap.get("afn-listing-exists").toString());
        int afnWarehouseQuantityIndex = Integer.valueOf(titleMap.get("afn-warehouse-quantity").toString());
        int afnFulfillableQuantityIndex = Integer.valueOf(titleMap.get("afn-fulfillable-quantity").toString());
        int afnUnsellableQuantityIndex = Integer.valueOf(titleMap.get("afn-unsellable-quantity").toString());
        int afnReservedQuantityIndex = Integer.valueOf(titleMap.get("afn-reserved-quantity").toString());
        int afnTotalQuantityIndex = Integer.valueOf(titleMap.get("afn-total-quantity").toString());
        int perUnitVolumeIndex = Integer.valueOf(titleMap.get("per-unit-volume").toString());
        int afnInboundWorkingQuantityIndex = Integer.valueOf(titleMap.get("afn-inbound-working-quantity").toString());
        int afnInboundShippedQuantityIndex = Integer.valueOf(titleMap.get("afn-inbound-shipped-quantity").toString());
        int afnInboundReceivingQuantityIndex = Integer.valueOf(titleMap.get("afn-inbound-receiving-quantity").toString());
        List<Object> insertList = new ArrayList<>(lists.size());
        for (int i = 1; i < lists.size(); i++) {
            MwsManageFbaInventory mwsManageFbaInventory = new MwsManageFbaInventory();
            List<String> stringList = lists.get(i);
            mwsManageFbaInventory.setShop(shop);
            mwsManageFbaInventory.setArea(area);
            mwsManageFbaInventory.setSku(stringList.get(skuIndex));
            mwsManageFbaInventory.setFnsku(stringList.get(fnskuIndex));
            mwsManageFbaInventory.setAsin(stringList.get(asinIndex));
            mwsManageFbaInventory.setProductName(stringList.get(productNameIndex));
            mwsManageFbaInventory.setCondition(stringList.get(conditionNameIndex));
            mwsManageFbaInventory.setYourPrice(stringList.get(yourPriceIndex));
            mwsManageFbaInventory.setMfnListingExists(stringList.get(mfnListingExistsIndex));
            mwsManageFbaInventory.setMfnFulfillableQuantity(stringList.get(mfnFulfillableQuantityIndex));
            mwsManageFbaInventory.setAfnListingExists(stringList.get(afnListingExistsIndex));
            mwsManageFbaInventory.setAfnWarehouseQuantity(stringList.get(afnWarehouseQuantityIndex));
            mwsManageFbaInventory.setAfnFulfillableQuantity(stringList.get(afnFulfillableQuantityIndex));
            mwsManageFbaInventory.setAfnUnsellableQuantity(stringList.get(afnUnsellableQuantityIndex));
            mwsManageFbaInventory.setAfnReservedQuantity(stringList.get(afnReservedQuantityIndex));
            mwsManageFbaInventory.setAfnTotalQuantity(stringList.get(afnTotalQuantityIndex));
            mwsManageFbaInventory.setPerUnitVolume(stringList.get(perUnitVolumeIndex));
            mwsManageFbaInventory.setAfnInboundWorkingQuantity(stringList.get(afnInboundWorkingQuantityIndex));
            mwsManageFbaInventory.setAfnInboundShippedQuantity(stringList.get(afnInboundShippedQuantityIndex));
            mwsManageFbaInventory.setAfnInboundReceivingQuantity(stringList.get(afnInboundReceivingQuantityIndex));
            mwsManageFbaInventory.setCreateTime(DateUtil.getDate());
            mwsManageFbaInventory.setSellerId(shopMapper.getOneInfoByBrandName(shop).getSellerId());
            mwsManageFbaInventory.setCreateTime(DateUtil.getDate());
            mwsManageFbaInventory.setFinanceStatus(1);
            insertList.add(mwsManageFbaInventory);
        }
        return insertList;
    }

    /**
     * 预留库存  将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shop
     * @param area
     * @return
     */
    public List<Object> handleReservedInventory(List<List<String>> lists, String shop, String area) throws Exception {
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        int skuIndex = Integer.valueOf(titleMap.get("sku").toString());
        int fnskuIndex = Integer.parseInt(titleMap.get("fnsku").toString());
        int asinIndex = Integer.valueOf(titleMap.get("asin").toString());
        int productNameIndex = Integer.valueOf(titleMap.get("product-name").toString());
        int reservedQtyIndex = Integer.valueOf(titleMap.get("reserved_qty").toString());
        int reservedCustomerordersIndex = Integer.valueOf(titleMap.get("reserved_customerorders").toString());
        int reservedFcTransfersIndex = Integer.valueOf(titleMap.get("reserved_fc-transfers").toString());
        int reservedFcProcessingIndex = Integer.valueOf(titleMap.get("reserved_fc-processing").toString());
        List<Object> insertList = new ArrayList<>(lists.size());
        for (int i = 1; i < lists.size(); i++) {
            MwsReservedInventory mwsReservedInventory = new MwsReservedInventory();
//            ReservedInventory reservedInventory = new ReservedInventory();
            List<String> stringList = lists.get(i);
            mwsReservedInventory.setShop(shop);
            mwsReservedInventory.setArea(area);
            mwsReservedInventory.setSku(stringList.get(skuIndex));
            mwsReservedInventory.setFnsku(stringList.get(fnskuIndex));
            mwsReservedInventory.setAsin(stringList.get(asinIndex));
            mwsReservedInventory.setProductName(stringList.get(productNameIndex));
            mwsReservedInventory.setReservedQty(ToolUtil.valueOfInteger(stringList.get(reservedQtyIndex)));
            mwsReservedInventory.setReservedCustomerorders(ToolUtil.valueOfInteger(stringList.get(reservedCustomerordersIndex)));
            mwsReservedInventory.setReservedFcTransfers(ToolUtil.valueOfInteger(stringList.get(reservedFcTransfersIndex)));
            mwsReservedInventory.setReservedFcProcessing(ToolUtil.valueOfInteger(stringList.get(reservedFcProcessingIndex)));
            mwsReservedInventory.setCreateTime(DateUtil.getDate());
            mwsReservedInventory.setSellerId(shopMapper.getOneInfoByBrandName(shop).getSellerId());
            mwsReservedInventory.setCreateTime(DateUtil.getDate());
            mwsReservedInventory.setFinanceStatus(1);
            insertList.add(mwsReservedInventory);
        }
        return insertList;
    }

    /**
     * 所有订单 将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shop
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleAllOrder(List<List<String>> lists, String shop, String area) throws Exception {
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i).trim(), i);
        }
        int amazonOrderIdIndex = Integer.valueOf(titleMap.get("amazon-order-id").toString());
        int merchantOrderIdIndex = Integer.valueOf(titleMap.get("merchant-order-id").toString());
        int purchaseDateIndex = Integer.valueOf(titleMap.get("purchase-date").toString());
        int lastUpdatedDateIndex = Integer.valueOf(titleMap.get("last-updated-date").toString());
        int orderStatusIndex = Integer.valueOf(titleMap.get("order-status").toString());
        int fulfillmentChannelIndex = Integer.valueOf(titleMap.get("fulfillment-channel").toString());
        int salesChannelIndex = Integer.valueOf(titleMap.get("sales-channel").toString());
        int orderChannelIndex = Integer.valueOf(titleMap.get("order-channel").toString());
        int urlIndex = Integer.valueOf(titleMap.get("url").toString());
        int shipServiceLevelIndex = Integer.valueOf(titleMap.get("ship-service-level").toString());
        int productNameIndex = Integer.valueOf(titleMap.get("product-name").toString());
        int skuIndex = Integer.valueOf(titleMap.get("sku").toString());
        int asinIndex = Integer.valueOf(titleMap.get("asin").toString());
        int itemStatusIndex = Integer.valueOf(titleMap.get("item-status").toString());
        int quantityIndex = Integer.valueOf(titleMap.get("quantity").toString());
        int currencyIndex = Integer.valueOf(titleMap.get("currency").toString());
        int itemPriceIndex = Integer.valueOf(titleMap.get("item-price").toString());
        int itemTaxIndex = Integer.valueOf(titleMap.get("item-tax").toString());
        int shippingPriceIndex = Integer.valueOf(titleMap.get("shipping-price").toString());
        int shippingTaxIndex = Integer.valueOf(titleMap.get("shipping-tax").toString());
        int giftWrapPriceIndex = Integer.valueOf(titleMap.get("gift-wrap-price").toString());
        int giftWrapTaxIndex = Integer.valueOf(titleMap.get("gift-wrap-tax").toString());
        int itemPromotionDiscountIndex = Integer.valueOf(titleMap.get("item-promotion-discount").toString());
        int shipPromotionDiscountIndex = Integer.valueOf(titleMap.get("ship-promotion-discount").toString());
        int shipStateIndex = Integer.valueOf(titleMap.get("ship-state").toString());
        int shipPostalCodeIndex = Integer.valueOf(titleMap.get("ship-postal-code").toString());
        int shipCountryIndex = Integer.valueOf(titleMap.get("ship-country").toString());
        int promotionIdsIndex = Integer.valueOf(titleMap.get("promotion-ids").toString());
        int isBusinessOrderIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("is-business-order")) {
            isBusinessOrderIndex = Integer.valueOf(titleMap.get("is-business-order").toString());
        }
        int purchaseOrderNumberIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("purchase-order-number")) {
            purchaseOrderNumberIndex = Integer.valueOf(titleMap.get("purchase-order-number").toString());
        }
        int priceDesignationIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("price-designation")) {
            priceDesignationIndex = Integer.valueOf(titleMap.get("price-designation").toString());
        }
        int customizedUrlIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("customized-url")) {
            customizedUrlIndex = Integer.valueOf(titleMap.get("customized-url").toString());
        }
        int customizedPageIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("customized-page")) {
            customizedPageIndex = Integer.valueOf(titleMap.get("customized-page").toString());
        }
        int isSoldByAbIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("is-sold-by-ab")) {
            isSoldByAbIndex = Integer.valueOf(titleMap.get("is-sold-by-ab").toString());
        }
        List<Object> insertList = new ArrayList<>(lists.size());
        for (int i = 1; i < lists.size(); i++) {
            List<String> stringList = lists.get(i);
            MwsAllOrder mwsAllOrder = new MwsAllOrder();
            mwsAllOrder.setAmazonOrderId(stringList.get(amazonOrderIdIndex));
            mwsAllOrder.setSku(stringList.get(skuIndex));
            mwsAllOrder.setAsin(stringList.get(asinIndex));
            mwsAllOrder.setLastUpdatedDate(DateUtil.ISO8601ToGMT(stringList.get(lastUpdatedDateIndex)));
            MwsAllOrder oneInfoByAmazonOrderIdAndSkuAndAsin = mwsAllOrderMapper.getOneInfoByAmazonOrderIdAndSkuAndAsin(
                    mwsAllOrder.getAmazonOrderId(), mwsAllOrder.getSku(), mwsAllOrder.getAsin(), area);
            //如果存在该条数据
            if (null != oneInfoByAmazonOrderIdAndSkuAndAsin && null != oneInfoByAmazonOrderIdAndSkuAndAsin.getId()) {
                //如果最后更新时间和本次获取的最后更新时间一致
                if (oneInfoByAmazonOrderIdAndSkuAndAsin.getLastUpdatedDate().equals(mwsAllOrder.getLastUpdatedDate())) {
                    continue;
                }
            }
            mwsAllOrder.setShop(shop);
            mwsAllOrder.setArea(area);
            mwsAllOrder.setMerchantOrderId(stringList.get(merchantOrderIdIndex));
            mwsAllOrder.setPurchaseDate(DateUtil.ISO8601ToGMT(stringList.get(purchaseDateIndex)));
            mwsAllOrder.setOrderStatus(stringList.get(orderStatusIndex));
            mwsAllOrder.setFulfillmentChannel(stringList.get(fulfillmentChannelIndex));
            mwsAllOrder.setSalesChannel(stringList.get(salesChannelIndex));
            mwsAllOrder.setOrderChannel(stringList.get(orderChannelIndex));
            mwsAllOrder.setUrl(stringList.get(urlIndex));
            mwsAllOrder.setShipServiceLevel(stringList.get(shipServiceLevelIndex));
            mwsAllOrder.setProductName(stringList.get(productNameIndex));
            mwsAllOrder.setItemStatus(stringList.get(itemStatusIndex));
            mwsAllOrder.setQuantity(stringList.get(quantityIndex));
            mwsAllOrder.setCurrency(stringList.get(currencyIndex));
            mwsAllOrder.setItemPrice(stringList.get(itemPriceIndex));
            mwsAllOrder.setItemTax(stringList.get(itemTaxIndex));
            mwsAllOrder.setShippingPrice(stringList.get(shippingPriceIndex));
            mwsAllOrder.setShippingTax(stringList.get(shippingTaxIndex));
            mwsAllOrder.setGiftWrapPrice(stringList.get(giftWrapPriceIndex));
            mwsAllOrder.setGiftWrapTax(stringList.get(giftWrapTaxIndex));
            mwsAllOrder.setItemPromotionDiscount(stringList.get(itemPromotionDiscountIndex));
            mwsAllOrder.setShipPromotionDiscount(stringList.get(shipPromotionDiscountIndex));
            mwsAllOrder.setShipState(stringList.get(shipStateIndex));
            mwsAllOrder.setShipPostalCode(stringList.get(shipPostalCodeIndex));
            mwsAllOrder.setShipCountry(stringList.get(shipCountryIndex));
            mwsAllOrder.setPromotionIds(stringList.get(promotionIdsIndex));
            if (isBusinessOrderIndex != WRONG_NUMBER_SIGN) {
                mwsAllOrder.setIsBusinessOrder(stringList.get(isBusinessOrderIndex));
            }

            if (purchaseOrderNumberIndex != WRONG_NUMBER_SIGN) {
                mwsAllOrder.setPurchaseOrderNumber(stringList.get(purchaseOrderNumberIndex));
            }

            if (priceDesignationIndex != WRONG_NUMBER_SIGN) {
                mwsAllOrder.setPriceDesignation(stringList.get(priceDesignationIndex));
            }

            if (customizedUrlIndex != WRONG_NUMBER_SIGN) {
                mwsAllOrder.setPriceDesignation(stringList.get(customizedUrlIndex));
            }

            if (customizedPageIndex != WRONG_NUMBER_SIGN) {
                mwsAllOrder.setPriceDesignation(stringList.get(customizedPageIndex));
            }

            if (isSoldByAbIndex != WRONG_NUMBER_SIGN) {
                mwsAllOrder.setPriceDesignation(stringList.get(isSoldByAbIndex));
            }

            mwsAllOrder.setCreateTime(DateUtil.getDate());
            mwsAllOrder.setUpdateTime(DateUtil.getDate());
            mwsAllOrder.setSellerId(shopMapper.getOneInfoByBrandName(shop).getSellerId());
            mwsAllOrder.setFinanceStatus(1);
            //如果没有该数据---插入数据
            if (null == oneInfoByAmazonOrderIdAndSkuAndAsin || null == oneInfoByAmazonOrderIdAndSkuAndAsin.getId()) {
                mwsAllOrder.setId(null);
                insertList.add(mwsAllOrder);
            } else {
                mwsAllOrder.setId(oneInfoByAmazonOrderIdAndSkuAndAsin.getId());
                baseUpdate(mwsAllOrder);
            }
        }
        return insertList;
    }


    /**
     * 库龄   将获取到的数据转为实体对象数组
     *
     * @param lists
     * @param shop
     * @param area
     * @return
     * @throws Exception
     */
    public List<Object> handleInventoryAge(List<List<String>> lists, String shop, String area) throws Exception {
        List<String> titleList = lists.get(0);
        Map<String, Object> titleMap = new HashMap<>(titleList.size());
        for (int i = 0; i < titleList.size(); i++) {
            titleMap.put(titleList.get(i), i);
        }
        int snapshotDateIndex = Integer.valueOf(titleMap.get("snapshot-date").toString());
        //获取数据库里面最新的日期
        String latestDay = mwsInventoryAgeMapper.getLatestDay(shop, area);
        if (!StringUtil.isBlank(latestDay)) {
            List<String> firstList = lists.get(1);
            //如果当前获取的数据的时间和数据库时间一致或者早于数据库时间，本次获取数据将跳过
            if (DateUtil.compateTime(latestDay, firstList.get(snapshotDateIndex)) >= 0) {
                return null;
            }
        }
        int skuIndex = Integer.valueOf(titleMap.get("sku").toString());
        int fnskuIndex = Integer.valueOf(titleMap.get("fnsku").toString());
        int asinIndex = Integer.valueOf(titleMap.get("asin").toString());
        int productNameIndex = Integer.valueOf(titleMap.get("product-name").toString());
        int conditionIndex = Integer.valueOf(titleMap.get("condition").toString());
        int avaliableQuantitySellableIndex = Integer.valueOf(titleMap.get("avaliable-quantity(sellable)").toString());
        int qtyWithRemovalsInProgressIndex = Integer.valueOf(titleMap.get("qty-with-removals-in-progress").toString());
        int invAge0To90DaysIndex = Integer.valueOf(titleMap.get("inv-age-0-to-90-days").toString());
        int invAge91To180DaysIndex = Integer.valueOf(titleMap.get("inv-age-91-to-180-days").toString());
        int invAge181To270DaysIndex = Integer.valueOf(titleMap.get("inv-age-181-to-270-days").toString());
        int invAge271To365DaysIndex = Integer.valueOf(titleMap.get("inv-age-271-to-365-days").toString());
        int invAge365PlusDaysIndex = Integer.valueOf(titleMap.get("inv-age-365-plus-days").toString());
        int currencyIndex = Integer.valueOf(titleMap.get("currency").toString());
        int qtyToBeChargedLtsf6MoIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("qty-to-be-charged-ltsf-6-mo")) {
            qtyToBeChargedLtsf6MoIndex = Integer.valueOf(titleMap.get("qty-to-be-charged-ltsf-6-mo").toString());
        }
        int projectedLtsf6MoIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("projected-ltsf-6-mo")) {
            projectedLtsf6MoIndex = Integer.valueOf(titleMap.get("projected-ltsf-6-mo").toString());
        }
        int qtyToBeChargedLtsf12MoIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("qty-to-be-charged-ltsf-12-mo")) {
            qtyToBeChargedLtsf12MoIndex = Integer.valueOf(titleMap.get("qty-to-be-charged-ltsf-12-mo").toString());
        }
        int projectedLtsf12MoIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("projected-ltsf-12-mo")) {
            projectedLtsf12MoIndex = Integer.valueOf(titleMap.get("projected-ltsf-12-mo").toString());
        }
        int unitsShippedLast7DaysIndex = Integer.valueOf(titleMap.get("units-shipped-last-7-days").toString());
        int unitsShippedLast30DaysIndex = Integer.valueOf(titleMap.get("units-shipped-last-30-days").toString());
        int unitsShippedLast60DaysIndex = Integer.valueOf(titleMap.get("units-shipped-last-60-days").toString());
        int unitsShippedLast90DaysIndex = Integer.valueOf(titleMap.get("units-shipped-last-90-days").toString());
        int alertIndex = Integer.valueOf(titleMap.get("alert").toString());
        int yourPriceIndex = Integer.valueOf(titleMap.get("your-price").toString());
        int salesPriceIndex = Integer.valueOf(titleMap.get("sales_price").toString());
        int lowestPriceNewIndex = Integer.valueOf(titleMap.get("lowest_price_new").toString());
        int lowestPriceUsedIndex = Integer.valueOf(titleMap.get("lowest_price_used").toString());
        int recommendedActionIndex = Integer.valueOf(titleMap.get("Recommended action").toString());
        int healthyInventoryLevelIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("Healthy Inventory Level")) {
            healthyInventoryLevelIndex = Integer.valueOf(String.valueOf(titleMap.get("Healthy Inventory Level")));
        }
        int recommendedSalesPriceIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("Recommended sales price")) {
            recommendedSalesPriceIndex = Integer.valueOf(String.valueOf(titleMap.get("Recommended sales price")));
        }
        int recommendedSaleDurationIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("Recommended sale duration (days)")) {
            recommendedSaleDurationIndex = Integer.valueOf(String.valueOf(titleMap.get("Recommended sale duration (days)")));
        }
        int recommendedRemovalQuantityIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("Recommended Removal Quantity")) {
            recommendedRemovalQuantityIndex = Integer.valueOf(String.valueOf(titleMap.get("Recommended Removal Quantity")));
        }
        int estimatedCoseSavingsOfRemovalIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("Estimated cost savings of removal")) {
            estimatedCoseSavingsOfRemovalIndex = Integer.valueOf(String.valueOf(titleMap.get("Estimated cost savings of removal")));
        }
        int sellThroughIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("sell-through")) {
            sellThroughIndex = Integer.valueOf(String.valueOf(titleMap.get("sell-through")));
        }
        int cubicFeetIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("cubic-feet")) {
            cubicFeetIndex = Integer.valueOf(String.valueOf(titleMap.get("cubic-feet")));
        }
        int storageTypeIndex = WRONG_NUMBER_SIGN;
        if (null != titleMap.get("storage-type")) {
            storageTypeIndex = Integer.valueOf(String.valueOf(titleMap.get("storage-type")));
        }
        List<Object> list = new ArrayList<>(lists.size());
        for (int i = 1; i < lists.size(); i++) {
            MwsInventoryAge inventoryAge = new MwsInventoryAge();
            List<String> stringList = lists.get(i);
            String currencySign = stringList.get(currencyIndex);
            for (int index = 0; index < stringList.size(); index++) {
                if (stringList.get(index).contains(currencySign)) {
                    if (stringList.get(index).equals(currencySign)) {
                        continue;
                    }
                    stringList.set(index, stringList.get(index).substring(currencySign.length() + 1));
                }
            }
            inventoryAge.setShop(shop);
            inventoryAge.setSellerId(shopMapper.getOneInfoByBrandName(shop).getSellerId());
            inventoryAge.setArea(area);
            inventoryAge.setSnapshotDate(stringList.get(snapshotDateIndex));
            inventoryAge.setSku(stringList.get(skuIndex));
            inventoryAge.setFnsku(stringList.get(fnskuIndex));
            inventoryAge.setAsin(stringList.get(asinIndex));
            inventoryAge.setProductName(stringList.get(productNameIndex));
            inventoryAge.setCondition(stringList.get(conditionIndex));
            inventoryAge.setAvaliableQuantitySellable(String.valueOf(stringList.get(avaliableQuantitySellableIndex)));
            inventoryAge.setQtyWithRemovalsInProgress(String.valueOf(stringList.get(qtyWithRemovalsInProgressIndex)));
            inventoryAge.setInvAge0To90Days(String.valueOf(stringList.get(invAge0To90DaysIndex)));
            inventoryAge.setInvAge91To180Days(String.valueOf(stringList.get(invAge91To180DaysIndex)));
            inventoryAge.setInvAge181To270Days(String.valueOf(stringList.get(invAge181To270DaysIndex)));
            inventoryAge.setInvAge271To365Days(String.valueOf(stringList.get(invAge271To365DaysIndex)));
            inventoryAge.setInvAge365PlusDays(String.valueOf(stringList.get(invAge365PlusDaysIndex)));
            inventoryAge.setCurrency(stringList.get(currencyIndex));
            if (qtyToBeChargedLtsf6MoIndex != WRONG_NUMBER_SIGN) {
                inventoryAge.setQtyToBeChargedLtsf6Mo(String.valueOf(stringList.get(qtyToBeChargedLtsf6MoIndex)));
            }
            if (projectedLtsf6MoIndex != WRONG_NUMBER_SIGN) {
                inventoryAge.setProjectedLtsf6Mo(String.valueOf(stringList.get(projectedLtsf6MoIndex)));
            }
            if (qtyToBeChargedLtsf12MoIndex != WRONG_NUMBER_SIGN) {
                inventoryAge.setQtyToBeChargedLtsf12Mo(String.valueOf(stringList.get(qtyToBeChargedLtsf12MoIndex)));
            }
            if (projectedLtsf12MoIndex != WRONG_NUMBER_SIGN) {
                inventoryAge.setProjectedLtsf12Mo(String.valueOf(stringList.get(projectedLtsf12MoIndex)));
            }
            inventoryAge.setUnitsShippedLast7Days(String.valueOf(stringList.get(unitsShippedLast7DaysIndex)));
            inventoryAge.setUnitsShippedLast30Days(String.valueOf(stringList.get(unitsShippedLast30DaysIndex)));
            inventoryAge.setUnitsShippedLast60Days(String.valueOf(stringList.get(unitsShippedLast60DaysIndex)));
            inventoryAge.setUnitsShippedLast90Days(String.valueOf(stringList.get(unitsShippedLast90DaysIndex)));
            inventoryAge.setAlert(stringList.get(alertIndex));
            inventoryAge.setYourPrice(stringList.get(yourPriceIndex));
            inventoryAge.setSalesPrice(stringList.get(salesPriceIndex));
            inventoryAge.setLowestPriceNew(String.valueOf(stringList.get(lowestPriceNewIndex)));
            inventoryAge.setLowestPriceUsed(String.valueOf(stringList.get(lowestPriceUsedIndex)));
            inventoryAge.setRecommendedAction(stringList.get(recommendedActionIndex));
            inventoryAge.setCreatTime(DateUtil.getDate());
            inventoryAge.setUpdateTime(DateUtil.getDate());
            if (healthyInventoryLevelIndex != WRONG_NUMBER_SIGN) {
                inventoryAge.setHealthyInventoryLevel(String.valueOf(stringList.get(healthyInventoryLevelIndex)));
            }
            if (recommendedSalesPriceIndex != WRONG_NUMBER_SIGN) {
                inventoryAge.setRecommendedSalesPrice(String.valueOf(stringList.get(recommendedSalesPriceIndex)));
            }
            if (recommendedSaleDurationIndex != WRONG_NUMBER_SIGN) {
                inventoryAge.setRecommendedSaleDurationDays(String.valueOf(stringList.get(recommendedSaleDurationIndex)));
            }
            if (recommendedRemovalQuantityIndex != WRONG_NUMBER_SIGN) {
                inventoryAge.setRecommendedRemovalQuantity(String.valueOf(stringList.get(recommendedRemovalQuantityIndex)));
            }
            if (estimatedCoseSavingsOfRemovalIndex != WRONG_NUMBER_SIGN) {
                inventoryAge.setEstimatedCostSavingsOfRemoval(stringList.get(estimatedCoseSavingsOfRemovalIndex));
            }
            if (sellThroughIndex != WRONG_NUMBER_SIGN) {
                inventoryAge.setSellThrough(stringList.get(sellThroughIndex));
            }
            if (cubicFeetIndex != WRONG_NUMBER_SIGN) {
                inventoryAge.setCubicFeet(stringList.get(cubicFeetIndex));
            }
            if (storageTypeIndex != WRONG_NUMBER_SIGN) {
                inventoryAge.setStorageType(stringList.get(storageTypeIndex));
            }
            inventoryAge.setFinanceStatus(1);
            list.add(inventoryAge);
        }
        return list;
    }


    /**
     * 发送GetReportList请求 获取ReportId
     *
     * @param params
     * @return
     * @throws Exception
     */
    public Map<String, Object> sendGetReportListByPayment(Map<String, String> params) throws Exception {
        return AmazonMwsApi.sendGetReportListByPayment(params);
    }


    /**
     * 发送GetReportList请求 获取ReportId
     *
     * @param params
     * @return
     * @throws Exception
     */
    public Map<String, Map<String, String>> sendGetReportList(Map<String, String> params) throws Exception {
        return handleGetReportList(params);
    }

    /**
     * 发送 GetReport 请求 获取报告
     *
     * @param params
     * @return
     * @throws Exception
     */
    private Map<String, Map<String, Object>> handleGetReport(Map<String, String> params) throws Exception {
        return AmazonMwsApi.sendGetReport(params);
    }

    /**
     * 发送 GetReportList 请求 获取ReportId
     *
     * @param params
     * @return
     * @throws Exception
     */
    private Map<String, Map<String, String>> handleGetReportList(Map<String, String> params) throws Exception {
        return AmazonMwsApi.sendGetReportList(params);
    }

    /**
     * 发送 RequestReport 请求 获取 RequestReportId
     *
     * @param params
     * @return
     * @throws Exception
     */
    private Map<String, Map<String, String>> handleRequestReport(Map<String, String> params) throws Exception {
        return AmazonMwsApi.sendRequestReport(params);
    }

}
